I have a summary table as below
user_id service no_of_trx
1 A 56
1 C 43
1 B 22
2 C 10
2 A 3
3 B 45
3 C 7
4 A 77
4 B 63
It summarizes all the different types of services a user_id has used, sorted by the number of transactions they make per service. How do I extract How many times each service appears as the top service? Expected results
service occurrence_as_max
A 2
B 1
C 1
Because service A is the top service for users 1 and 4, and services B and C are top services for users 3 and 2 respectively.
What I have so far:
WITH a as
(SELECT user_id, service, count(service) no_of_trx
FROM transactions
GROUP BY user_id, service
ORDER BY no_of_trx desc),
b as
(SELECT distinct(user_id) user, max(no_of_trx) occurrence_as_max
FROM a
GROUP BY user_id
ORDER by user)
SELECT distinct(service), b.occurrence_as_max
FROM b
LEFT JOIN a ON a.user_id=b.user.
ORDER by b.occurrence_as_max desc;
But this clearly will not work.
This following script should work. This is standard query syntax. You may required some adjustment in BigQuery but the logic should be OK.
SELECT A.service, COUNT(*)
FROM your_table A
INNER JOIN
(
SELECT user_id, MAX(no_of_trx) no_of_trx
FROM your_table
GROUP BY user_id
)B ON A.user_id = B.user_id
AND A.no_of_trx = B.no_of_trx
GROUP BY A.service
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments