Need SQL help ranking users by combining data from various tables

Question

users

   id  | first_name
 ------+------------
    1 | Ann
    2 | Heidi
    3 | Becky
    4 | Steven
    5 | Jenny

service1

  id  |     user_id |       fees
------+-------------+-----------
    1 |           3 |    267732
    2 |           1 |     68647
    3 |           2 |     45954
    4 |           1 |     44655
    5 |           5 |     42957

service2

  id  |     user_id |       fees
------+-------------+-----------
    1 |           3 |      2632
    2 |           1 |         0
    3 |           2 |      4954
    4 |           1 |      4355
    5 |           5 |      2927

Here is what my result would be:

   first_name  | total_fees
 --------------+------------
    Becky      |     270364
    Ann        |     117657
    Heidi      |      50908
    Jenny      |      45884
    Steven     |          0

I am a little new at this, but am having a small issues with creating a query to rank the users with highest total fees. Here's a my crack of the queries. Basically I want to return the name and fees.

SELECT first_name, ((SELECT SUM(fees) FROM service1 WHERE service1.user_id = user.id) + (SELECT SUM(fees) FROM service2 WHERE service2.user_id = user.id)) AS total_fees FROM users ORDER BY total_fees DESC NULLS LAST LIMIT 10;

Answer 1

First, you need to get the total of the fees for all the users:

select u.*, (coalesce(s1.fee, 0) + coalesce(s2.fee, 0)) as total_fee
from users u left join
     (select user_id, sum(fees) as fee
      from service1
      group by user_id
     ) s1
     on u.id = s1.user_id left join
     (select user_id, sum(fees) as fee
      from service2
      group by user_id
     ) s2
     on u.id = s2.user_id;

In Postgres, you can use the ANSI standard RANK() function to rank these (most databases support this functionality but not MySQL:

select u.*, (coalesce(s1.fee, 0) + coalesce(s2.fee, 0)) as total_fee,
       rank() over (order by (coalesce(s1.fee, 0) + coalesce(s2.fee, 0))) as therank
from users u left join
     (select user_id, sum(fees) as fee
      from service1
      group by user_id
     ) s1
     on u.id = s1.user_id left join
     (select user_id, sum(fees) as fee
      from service2
      group by user_id
     ) s2
     on u.id = s2.user_id;