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;

Need Your Help

Protocol Buffer Wireshark Plugin

tcp protocol-buffers wireshark wireshark-dissector

I am looking for a wireshark plugin for google protocol buffer. And I found this GPB Wireshark plugin http://code.google.com/p/protobuf-wireshark/

making a SOAP request not working

php soap

I am having this issue for more than a week now without being able to find a solution to it. I an trying to make a SOAP call where the required structure of the request is