I’m trying to get counts for distinct videos by viewership tier. I have the following table:
I tried using this code to get my desired output:
WHEN views < 1000 THEN '< 1K Views'
WHEN views >= 1000 AND views < 10000 THEN '1K to 10K Views'
WHEN views >= 10000 AND views < 100000 THEN '10K to 100K Views'
WHEN views >= 100000 AND views < 1000000 THEN '100K to 1M Views'
ELSE '1M+ Views'
END AS tier,
Since there are multiple entries for each
vid_id, I want to group the counts of the videos for each tier using the max number of views for each
vid_id. So my output should be:
< 1K Views 1
100K to 1M Views 2
Video #1 reached 900 views max, so it’s in the
< 1K Views tier. Videos 2 and 3 reached 125,000 and 150,000 views, respectively. So they are in the
100K to 1M Views tier.
Hey, Justin. Great to see you here!
I would first filter the starting table for the rows that match the maximum for each video, i.e., I’d obtain a table that looks like this:
Then you can use the code in your question on this table. So how do we get a table that looks like this?
Expand to see a solution.
FROM test as T1
LEFT JOIN (SELECT vid_id, MAX(views) as max_views
GROUP BY 1
) AS T2
ON T1.vid_id = T2.vid_id and T1.views = T2.max_views
This is untested code. Let me know if it works!
Edit: Han’s observations are absolutely right, but they don’t take away from the main point of my answer. You just need to tweak it like that Han described.
Hi Justin if you look at sql order of execution: https://sqlbolt.com/lesson/select_queries_order_of_execution
You can see FROM and GROUP BY come before select. The GROUP BY you currently have will not recognize what “tier” means because it’s undefined yet.
You can wrap the whole SELECT statement in (), treat this as a new table, and write another select from (newtable) groupby over it. Alternatively you can avoid nesting by abstracting out the nested SELECT using CREATE VIEW to improve readability
Thanks, Bruno! I’ll give this a try. I found an alternate solution that seems to get the job done well.
Thanks, @hanqi! I’ll give your approach a try too.