# postgreSQL ranking query with the given user_id

I am trying to **get rank of a user** by their two dimension params: donation sum and total donor count.

My rank formula is: rank of [rank of donation_sum + rank of donor_count / 2]

Sample table:

donation_id | user_id | donor_id | donation_sum ----------------------------------------------- 1 | 1 | 1 | 10 2 | 1 | 2 | 5 3 | 2 | 3 | 10 4 | 3 | 1 | 50 ...

As you see, some donors make donation to different users, so I used sum(donation_sum) and count(distinct(donation_id)) to get exact rankings

I am able to get list of ranking separately by donation sum and total donor count with 2 sql but my need is to get a user rank with that formula above by given user_id in postgreSQL v. 9.4

Do you have any solution for it? so I will use that sql query in a Yii2 PHP framework

Thanks

Edit: We added donation_date to the tbl_donation and modified actual query as below:

##### is it true usage of where donation_date?

with list as ( select s.runner_id, sum, count, rank_sum, rank_count, (rank_sum+ rank_count)::float/ 2 as rank_avg, row_number() over (order by rank_sum) as rank from ( select *, rank() over (order by sum desc) rank_sum from ( select runner_id, sum(donation_sum) from tbl_donation where donation_date >= '2015-01-01' group by 1 ) s ) s join ( select *, rank() over (order by count desc) rank_count from ( select runner_id, count(distinct(donator_id)) from tbl_donation where donation_date >= '2015-01-01' group by 1 ) c ) c using (runner_id) ) select rank from list where runner_id = 251;

## Answers

Make two rankings in separate subqueries:

select s.user_id, sum, count, rank_sum, rank_count, (rank_sum+ rank_count)::float/ 2 as rank_avg, row_number() over (order by rank_sum) as rank from ( select *, rank() over (order by sum desc) rank_sum from ( select user_id, sum(donation_sum) from donations group by 1 ) s ) s join ( select *, rank() over (order by count desc) rank_count from ( select user_id, count(distinct(donation_id)) from donations group by 1 ) c ) c using (user_id); user_id | sum | count | rank_sum | rank_count | rank_avg | rank ---------+-----+-------+----------+------------+----------+------ 3 | 100 | 1 | 1 | 2 | 1.5 | 1 1 | 30 | 2 | 2 | 1 | 1.5 | 2 2 | 20 | 1 | 3 | 2 | 2.5 | 3 (3 rows)

If you want to select rank for a single user_id use with query, e.g.:

with list as ( -- place here the above query ) select rank from list where user_id = 2;