SQL Top 10% of the sum of events per person

Database

SQL Top 10% of the sum of events per person

Hi - I have a database with 3 columns - sub_id, amount_paid, date

The sub_id may have paid multiple times per month in different amounts each time.

I need to pull back the Top 10% unique sub_id's where the query has summed up their overall payment between X and Y date


Accepted Solutions
Junior Contributor

Re: SQL Top 10% of the sum of events per person

 

select top 10 percent sub_id, sum(amount_paid) as sumamt
from mytab
group by 1
order by sumamt desc

or

select sub_id, sum(amount_paid) as sumamt
from mytab
group by 1
qualify percent_rank() over (order by sumamt desc) <= 0.1
1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: SQL Top 10% of the sum of events per person


The sub_id may have paid multiple times per month in different amounts each time.

I need to pull back the Top 10% unique sub_id's where the query has summed up their overall payment between X and Y date


You need to add more details:

Each amount of a sub_id individually or aggregated?

How to sum the amounts, ordered by what?

 

 

Re: SQL Top 10% of the sum of events per person

Hi - so I need to see the sum of each individual Sub_ID ordered from largest paid to smallest between 1st April to 30th June and then only use the top 10% of those sub_ids ie if there were 200 sub_ids I would be taking only the first 20 of these as they have paid the most. I hope this makes sense

Junior Contributor

Re: SQL Top 10% of the sum of events per person

 

select top 10 percent sub_id, sum(amount_paid) as sumamt
from mytab
group by 1
order by sumamt desc

or

select sub_id, sum(amount_paid) as sumamt
from mytab
group by 1
qualify percent_rank() over (order by sumamt desc) <= 0.1

Re: SQL Top 10% of the sum of events per person

Much appreciated!! This worked for me - thanks