CODEWITHSUNDEEP
sqlgoogle-bigquery
ambe5960
ambe5960

Reputation: 1990

Select maximum category for each user

This is a common question on SO, it seems, but I can't wrap my head around a solution for my specific data. I have a query which finds total amounts for certain categories of user purchases. I need to then select the category with the highest total for each user. I couldn't get this to work with "TOP 1" solutions, which may have had something to do with google bigquery, or may have been me not being a real data analyst :)

Select * 
from users
left join (
  SELECT
    sum(CHARGE_AMT) as category_total,
    user_id,
    category_name
  FROM
    `table1`
  GROUP BY
    user_id, category_name
  enter code here
) as category_totals on category_totals.user_id = user_id

These are the results of the subquery/join table

Total | user_id | category
------+---------+--------
40200 | 1       | Orange
1140.1| 1       | Yellow
3090  | 2       | Green 
220.8 | 2       | red
6420.2| 2       | Blue

This is what I would expect to be the results

Total | user_id | category
------+---------+--------
40200 | 1       | Orange
6420.2| 2       | Blue
111111| 3       | Rainbow

Upvotes: 0

Views: 83

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Apply below to your current result

select *
from your_result
qualify 1 = row_number() over(partition by user_id order by total desc)

or you can just add below to your current query

qualify 1 = row_number() over(partition by user_id order by total desc)

Upvotes: 2

Related Questions