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.
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.