Interview Question Bank · Timestamp to date · COUNT(DISTINCT) · HAVING
1. Binge-watch days
MediumStreamingQuestion #127
Concept: Timestamp to date · COUNT(DISTINCT) · HAVINGshow
Event tables store timestamps, so 'the same day' means casting to a date first: started_at::date. Rewatches and replays create several rows for the same item, so count COUNT(DISTINCT item) when the question is about different items. HAVING then keeps only the groups that clear the bar.
Example (different data):
SELECT customer_id, visited_at::date AS visit_day, COUNT(DISTINCT store_id) AS stores FROM store_visits GROUP BY customer_id, visited_at::date HAVING COUNT(DISTINCT store_id) >= 2;
Your task
The recommendations team at Reelquarry, a video streaming service, defines a binge as starting 3 or more different episodes of one show on the same calendar day.
Return user_id, show (the show's title), watch_date (the date of started_at) and episodes (distinct episodes of that show the user started that day) for every binge: 3 or more different episodes of the same show on the same day. Sort by watch_date, user_id, show.
Starting Postgres in your browser…
Checking your access…