Posted by gurpreet, Nov. 20, 2021, 10:05 p.m.
SQL#171
I am getting the correct answer using the following query, but the solution provided with question has only 'customer_id' in group by clause, should it not contain date as well since we are summing streaming time per day?
WITH categorised_customers as (
SELECT stream_dt,
customer_id,
case when sum(minutes_streamed)*1.0/60 >= 5 then 'binge'
else 'not-binge' end as category
FROM video_stream
WHERE stream_dt >= '2021-08-01' AND stream_dt < '2021-09-01'
AND customer_country = 'US'
GROUP BY stream_dt, customer_id
)
SELECT customer_id
FROM categorised_customers
WHERE category = 'binge'
WITH categorised_customers as (
SELECT stream_dt,
customer_id,
case when sum(minutes_streamed)*1.0/60 >= 5 then 'binge'
else 'not-binge' end as category
FROM video_stream
WHERE stream_dt >= '2021-08-01' AND stream_dt < '2021-09-01'
AND customer_country = 'US'
GROUP BY stream_dt, customer_id
)
SELECT customer_id
FROM categorised_customers
WHERE category = 'binge'
