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…