Dividing A Variable Amount of Rows Into Buckets and Dividing Into Deciles

Database
Fan

Dividing A Variable Amount of Rows Into Buckets and Dividing Into Deciles

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.

2 REPLIES
Fan

Re: Dividing A Variable Amount of Rows Into Buckets and Dividing Into Deciles

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. 

Highlighted
Teradata Employee

Re: Dividing A Variable Amount of Rows Into Buckets and Dividing Into Deciles

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())