Quartile fn

Analytics
Enthusiast

Quartile fn

Hi,

We have a requirement where we have to show top regions based on quartile calculations. So if for example we have 48 regions, we would display 12th,24th,36th region. The no. of regions an change in future..it can be 200 as well. In that case we need to display 50,100 and so on. Can this be done dynamically? and if so how?

TIA
1 REPLY
Junior Contributor

Re: Quartile fn

Hi adash,
there's a QUANTILE function, but it's outdated and should be replaced by ANSI syntax.

And the nth region can be calculated using MOD, e.g. for 48 quantiles:

SELECT
48 * (RANK() OVER (ORDER BY COL) - 1) / COUNT(*) OVER() AS q
...
QUALIFY
q MOD 12 = 0

Dieter