Drill 3 of 3 in Streaming questions
0

Interview Question Bank · Relational division · HAVING COUNT(DISTINCT) = total

3. Watched every episode of a season

HardStreamingQuestion #130

Concept: Relational division · HAVING COUNT(DISTINCT) = totalshow

'Users who did every X' is relational division: count the distinct X each user covered and compare it with the total number of X in HAVING. Take the total from the table that defines the set, not from the activity table, because an item nobody has touched never appears in the activity. Count DISTINCT on the activity side, so doing one item twice can't make up for one you skipped.

Example (different data):

SELECT s.student_id
FROM submissions s
JOIN assignments a ON a.assignment_id = s.assignment_id
WHERE a.course_id = 7
GROUP BY s.student_id
HAVING COUNT(DISTINCT s.assignment_id) =
  (SELECT COUNT(*) FROM assignments WHERE course_id = 7);

Your task

Reelquarry awards a 'season complete' badge to everyone who has watched every episode of a season, however many days it took them.

Return user_id, show (the show's title) and season for every user who has at least one view of every episode in that season of that show. Sort by user_id, show, season.

Starting Postgres in your browser…

Checking your access…