MYSQL arrange users based on 2 columns from 2 different tables

Question

I'm looking for a possible solution that will give me TOP 10 users from users table who are ordered by the amount earned and referrals they have from 2 tables. Total referrals from the same users table using inviter_id column and amount column from another table called transactions.

So here is my table schema for users and transactions table.

users TABLE

id     inviter_id
1      1
2      1
3      1
4      1
5      2
6      3
7      5
8      6
9      1
10     3
11     9
12     1
13     5
14     7
15     11

EXPLANATION : The id represents user's unique ID and inviter_id represents the ID that invited the user.

transactions TABLE

id     receiver_id amount
1      1           200
2      1           100
3      1           50
4      2           10
5      3           400
6      4           200
7      5           100
8      6           50
9      7           100
10     8           50
11     9           50
12     10          100 
13     11          400
14     1           200
15     2           100
16     1           50
17     1           10
18     4           500

Here receiver_id is the users in users table.

Desired Output :

user_id     referrals     earned
1           6             610
2           1             110
3           2             400
4           0             700
5           2             100
6           1             50
7           1             100
8           0             50
9           1             50
10          0             100
11          1             400
12          0             0
13          0             0
14          0             0
15          0             0

EXPLANATION : I need the COUNT of referrals each user has made along with the earnings that user has made.

BONUS : I'll need the output table sorted based on who has highest referrals and earnings.

What I tried so far : Since I'm using codeigniter query builder, here is my code.

        $this->db->select('u.id, AS user_id, IF(COUNT(p.id) IS NULL, 0, COUNT(p.id)) AS referrals, IF(SUM(m.amount) IS NULL, 0, SUM(m.amount)) AS earned');
        $this->db->from('users u');
        $this->db->join('transactions m', "u.id = m.receiver_id",'LEFT');
        $this->db->join('users p', "u.id = p.inviter_id",'LEFT');

        $this->db->group_by('u.id');
        $this->db->limit(10);

        $this->db->order_by('referrals', 'DESC');
        $this->db->order_by('earned', 'DESC');
        $query = $this->db->get();
        $row = $query->result();

I'm getting wrong referrals and earnings values as I'm joining tables, the COUNT and SUM gives me multiples of rows joined.

Answer 1

If I understand correctly, you need to aggregate both tables. To keep all ids, use a left join and aggregate the tables before joining:

select u.id, coalesce(ui.referrals, 0), coalesce(t.earned, 0) as earned
from users u left join
     (select u.inviter_id, count(*) as referrals
      from users u
      group by u.inviter_id
     ) ui 
     on ui.inviter_id = u.id left join
     (select receiver_id, sum(amount) as earned
      from transactions t
      group by t.receiver_id
     ) t
     on t.receiver_id = u.inviter_id
group by u.id
order by referrals desc, earnings desc;