'SQL percent rank by increasing increments
I have this data:
number type dates subtype funds
118111 savings 21/05/2119 cash2 9026
119222 checking 21/12/2121 cash1 2574
120333 savings 11/06/2120 check1 3761
121444 savings 20/06/2122 check1 1661
122555 savings 12/08/2119 check1 7227
And I want to find for each group of type and sybtype the percentiles by increments of 0.005, starting through 0.005 up to 0.995 (0.005, 0.01, 0.015...).
For each row, I need to get the change of the amount from the previous percentile in percentage of amount.
savings cash2 1000 0.015 1%
savings cash2 1040 0.02 4%
and from there, for each pair I want to pull the data of only three rows that are AHEAD of the ones with the biggest percentage change. So if 4% is one of the 3 biggest change for this group, then the row with the 0.015 will be picked.
I've tried this:
SELECT type, subtype, funds, PERCENT_RANK()
over (partition by type, subtype
order by funds)
from myTable
so I get each pair and percentile, but it's not in increments and I'm unsure how to proceed.
EDIT:
Got up to here:
select type, subtype, funds, ranking
from (
SELECT type, subtype, amount,
((cast(funds as float)/cast(lag(funds)
OVER (partition by segment, subtype order by amount) as float))- 1) * 100 AS percentage,
ROUND(PERCENT_RANK() over (partition by type, subtype order by funds)/0.005,0) * 0.005 as percentile
from myTable) mt(type, subtype, funds,percentage,ranking)
where ranking between 0.75 and 0.995
order by ranking ASC```
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
