Блог

Я ищу возможное решение, которое даст мне ТОП-3 пользователей из users таблицы, которые упорядочены на основе turnover сделанных ими.

Итак, вот моя схема таблицы для users и transactions таблицы.

users ТАБЛИЦА

 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

 

ОБЪЯСНЕНИЕ: id Представляет уникальный идентификатор пользователя и inviter_id представляет идентификатор, который пригласил пользователя.

transactions ТАБЛИЦА

 id            payer _id      amount
1             2              200
2             2              100
3             2              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            2              200
15            2              100
16            2              50
17            2              10
18            4              500
 

Здесь payer_id представлены пользователи в users таблице.

Желаемый результат :

 user_id       turnover
//ID 1 should not be included in this table.
2             100         //ID 5 in `users` is referral of ID 2 so summing whatever ID 5 from `transactions` table using `payer_id` column we get : 100 (Sme process for other rows too)
3             150         //ID3's referrals : 6,10 | 50 100
4             0           //ID4's invited no one
5             100         //ID5's referrals : 7,13 | 100
6             50          //ID 6's referrals : 8 | 50
7             100         // ID 7's referrals : 14 | 0 (No rows for ID 14 in transactions table)
8             0           //No referrals invited
9             400         //ID9's referrals : 11 | 400
10            0           //No referrals invited
11            0           //ID11's referrals : 14 | 0 (No rows in txn table)
12            0           //No referrals invited
13            0           //No referrals invited
14            0           //No referrals invited
15            0           //No referrals invited
 

СНАЧАЛА позвольте мне объяснить, как я вычисляю turnover :

Для каждой id таблицы from users давайте возьмем id:1 в качестве примера. Я проверяю столбец ‘inviter_id’ на 2. Таким образом, у кого бы ни было значение 2 в столбце inviter_id, эти идентификаторы считаются ссылкой id 2. Итак, мне нужна сумма amount всех рефералов, которые заплатили id 2. Аналогично для других идентификаторов, и они должны быть отсортированы по наибольшему обороту.

Что я пробовал до сих пор: вот мой код.

         select u.id AS user_id, u.address AS address, SUM(turnover) AS turnover
        from users u left join
             (select ur.id, ur.inviter_id, SUM(mt.userTurnover) as turnover
                    from users ur left join

                            (select m.payer_id, SUM(m.amount) as userTurnover
                            from matrix m where m.created >= $content_start AND m.type = 'escrow'
                            group by m.payer_id
                            ) mt
                            on mt.payer_id = ur.id

                    where ur.created >= $content_start
              group by ur.inviter_id
             ) ui
             on ui.inviter_id = u.id
                 WHERE u.id != 1 AND u.id != 2 AND u.id != 3 AND u.id != 4
        group by u.id
        order by turnover desc  LIMIT 10;
 

Я получаю сумму сумм самого пользователя вместо выплаченной суммы каждого пользователя и каждого реферала.

 SELECT u1.id, COALESCE(SUM(tr.amount), 0) turnover
FROM users u1
LEFT JOIN users u2 ON u1.id = u2.inviter_id
LEFT JOIN transactions tr ON u2.id = tr.payer_id AND u2.id != u2.inviter_id
GROUP BY u1.id
 

скрипка

Если вам нужна общая сумма платежа для usrs.id = 1 (т. е. Включая ссылки на себя), затем удалите AND u2.id != u2.inviter_id .

Если вам не нужна последняя строка для users.id = 1 затем добавьте в соответствии с условием WHERE.