How can I extract the number of occurences where a value occurs as the MAX?

user_10326620 0
Viewed 43

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.

google-bigquery sql
3 Answers

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
user_36523450

Below is for BigQuery Standard SQL (no need for any self-joins) 

#standardSQL
SELECT service, COUNT(1) AS occurrence_as_max
FROM (
  SELECT STRING_AGG(service ORDER BY no_of_trx DESC LIMIT 1) service
  FROM `project.dataset.table`
  GROUP BY user_id
)
GROUP BY service

You can test, play with above using sample data from your question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 user_id, 'A' service, 56 no_of_trx UNION ALL
  SELECT 1, 'C', 43 UNION ALL
  SELECT 1, 'B', 22 UNION ALL
  SELECT 2, 'C', 10 UNION ALL
  SELECT 2, 'A', 3 UNION ALL
  SELECT 3, 'B', 45 UNION ALL
  SELECT 3, 'C', 7 UNION ALL
  SELECT 4, 'A', 77 UNION ALL
  SELECT 4, 'B', 63 
)
SELECT service, COUNT(1) AS occurrence_as_max
FROM (
  SELECT STRING_AGG(service ORDER BY no_of_trx DESC LIMIT 1) service
  FROM `project.dataset.table`
  GROUP BY user_id
)
GROUP BY service
-- ORDER BY service

with result 

Row service occurrence_as_max    
1   A       2    
2   B       1    
3   C       1
user_52219440

I would just use regular window functions for this:

select service, countif(seqnum = 1)
from (select t.*,
             row_number() over (partition by user_id order by no_of_trx desc) as seqnum
      from t
     ) t
group by service;

If you want ties to be counted, then use rank() instead of row_number().

user_11440350
Related