snowflake ntile of engagement rate over month

I'm trying to write a query that ranks a customer's engagement rate by decile within a month, against all other customers.

I tried:

 ntile(10) over (partition by rec_month order by engagement_rate) as decile

but I don't think that is getting me what I need. It appears to just be splitting the vhosts up into 10 equally sized groups. I want percentiles.

I also tried:

 ntile(10) over (partition by rec_month, vhost order by engagement_rate) as decile

But that is only calculating it within customer (vhost) within the month.

How do I calculate the engagement_rate decile against all other customers (Vhosts) within the month?

1 Answer

At first I suspect you a rank function, like DENSE_RANK or RANK (which is sparse, the on I'd use), and then turn that to a percentage by dividing by the count(*) and then truncating into deciles by (trunc(V/10)*10), but then I suspect you might want the output of the PERCENT_RANK function, but the snowflake documentation example is not as clarifying as I would hope, to know it solves your problem.

select column1, column2, round(percent_rank() over (order by column2),3) as p_rank, trunc(p_rank*10)*10 as decile
from values ('a',1),('b',2),('c',3),('d',4),('e',5),('f',6),('g',7);

gives

COLUMN1 COLUMN2 P_RANK DECILE
a 1 0 0
b 2 0.167 10
c 3 0.333 30
d 4 0.5 50
e 5 0.667 60
f 6 0.833 80
g 7 1 100

but maybe you want to use ntile instead of the truncating of the percentage. The round is just there to make the answers above less verbose.