How to create deciles using below approach (sql)

[Vijay1102]

Hi Folks,

I want to create custom decile for my project. Tile tool is not helping much in obtaining required results. I want to convert below sql code into Alteryx environment to get the appropriate deciles. Can anyone help me in this.

Thanks in advance!

(data attached for reference)

-- Code for Creating deciles

create or replace table identifier($tablef) as
with temp as (
select npi, count(distinct patient_id) as pats, count(distinct claim_id) as trx
from identifier($table2)
where npi is not null
group by 1
)
,
running as (
select *
, SUM(trx) OVER (ORDER BY trx DESC) as running_trx
, SUM(trx) OVER (ORDER BY trx DESC) / SUM(trx) OVER () as ratio
, 11 - ceil(10 * SUM(trx) OVER (ORDER BY trx DESC) / SUM(trx) OVER ()) as decile
from temp
)

[BS_THE_ANALYST]

@Vijay1102 Are you looking for something like this?

It's been a while since I've dug through SQL.

[Vijay1102]

Thanks for pitching in but above sql code is creating deciles using a ratio factor which I need to crack. Your solution is simply splitting entire data into 10 splits regardless of sales column which is key for this deciling.

[FinnCharlton]

@Vijay1102 , some sample data would be really useful in this case

[Vijay1102]

Data attached. TRx is as same as sales in demo.

[FinnCharlton · Accepted solution]

@Vijay1102 , this should match your SQL query. Cool way to do deciles, wouldn't have though of this.

[Vijay1102]

will implement this & get back to you. Thanks!

[Vijay1102]

Works flawlessly, Thanks again!

[Vijay1102]

Hi Finn,

Tried your solution & working to a large extend but I'm not able to get exact match. I tried to debug the possibilities but not through. Can you suggest where is the gap?

Thanks,