mysqlgroup-bycount

MySQL, join to find only max transaction count based of group


I am trying to find the max of transactions count based on type for each user.

id user_id type
1 1 A
2 1 B
3 1 C
4 1 A
5 2 B
6 2 C
7 2 C
8 2 C

I am expecting the output to be:

user_id type count
1 A 2
2 C 3
SELECT 
    DISTINCT(t.user_name), t.discom, c.*
FROM transactions AS t
LEFT JOIN (
    SELECT MAX(id) AS id, user_id, COUNT(id) AS `count` FROM transactions
    GROUP BY user_name, discom
) AS c ON c.user_id = t.user_id
GROUP BY t.user_name
ORDER BY c.count DESC

Although, the query is running, but its taking too much time(in minutes) just to fetch the data of mere 3500 users.


Solution

  • The solution:

    SELECT user_id, subquery_1.type, cnt
    FROM (
      SELECT user_id, type, COUNT(*) cnt
      FROM transactions
      GROUP BY user_id, type
      ) subquery_1
    JOIN ( 
      SELECT user_id, MAX(cnt) cnt
      FROM (
        SELECT user_id, type, COUNT(*) cnt
        FROM transactions
        GROUP BY user_id, type
        ) subquery_2
      GROUP BY user_id
      ) subquery_3 USING (user_id, cnt);
    

    On your version 5.5.62-cll the next query should produce correct output in most cases:

    SELECT user_id, type, MAX(cnt)
    FROM (
      SELECT user_id, type, COUNT(*) cnt
      FROM transactions
      GROUP BY user_id, type
      ORDER BY user_id, cnt DESC
      ) subquery
    GROUP BY user_id
    

    Pay attention - this is a trick which DOES NOT GUARANTEE that the output is correct.


    fiddle