Drill 1 of 3 in Streaming questions
0

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…