Drill 2 of 2 in Music streaming questions
0

Interview Question Bank · COUNT(DISTINCT date) · date_trunc('week') · HAVING

2. Same song on 3+ days in one week

HardMusic streamingQuestion #274

Concept: COUNT(DISTINCT date) · date_trunc('week') · HAVINGshow

'On N different days' means counting distinct dates, not rows: COUNT(DISTINCT ts::date). Counting DISTINCT on the raw timestamp counts events, because no two timestamps match. For calendar weeks, date_trunc('week', ts) snaps any moment to the Monday that starts its week; group by it alongside the other keys and filter the groups with HAVING.

Example (different data):

SELECT member_id, date_trunc('week', checked_in_at)::date AS week_start,
  COUNT(DISTINCT checked_in_at::date) AS gym_days
FROM gym_checkins
GROUP BY member_id, date_trunc('week', checked_in_at)
HAVING COUNT(DISTINCT checked_in_at::date) >= 4;

Your task

Echolane wants to find 'on repeat' listeners: users who came back to the same song on 3 or more different days within one calendar week (Monday to Sunday).

Return user_id, title, week_start (the Monday of the week, as a date), listen_days (distinct days that week with a play of that song) and plays (total plays of it that week) for every user, song and week with listen_days of 3 or more. Sort by week_start, user_id, then title.

Starting Postgres in your browser…

Checking your access…