Trying to write a script where it categorizes accounts into 10 deciles based on their balance size. The amount of accounts will change every month due to closing, transfers, etc. so I'm trying to envision a script that'll do all this but struggling.
Tried using quartile and also rank but getting stuck when I want the query to automatically calculate the amount of accounts, create the parameters for each decile, and add a label for each account.
Attempted to use
sel a.*, Quantile (10, ID, Balance)
from table a
The output seems to be 10 categories, however they don't seem to be ranked by or put into their bucket by their balance.
Why do you have ID in there then? You want the decile of the Balnce, so use Quantile(10, Balance).
Better to use Rank(), as it is a standard function, and Quantile() is deprecated: (Rank() over(order by Balance) -1) / (count(*) over())