Drill 5 of 5 in Streaming questions
0

Interview Question Bank · COUNT(DISTINCT) FILTER · HAVING · threshold comparisons

5. Title completion rate with a viewer minimum

MediumStreamingQuestion #268

Concept: COUNT(DISTINCT) FILTER · HAVING · threshold comparisonsshow

When people can repeat an action, count people with COUNT(DISTINCT person_id), and count the ones who met a condition with COUNT(DISTINCT person_id) FILTER (WHERE ...). Compare against a fraction of another column directly (watched >= 0.9 * total) rather than dividing two integers, which truncates to 0. Put a minimum sample size in HAVING so tiny groups don't top the chart.

Example (different data):

SELECT c.name,
  COUNT(DISTINCT a.student_id) AS students,
  COUNT(DISTINCT a.student_id) FILTER (WHERE a.score >= 0.8 * c.max_score) AS passed
FROM attempts a
JOIN courses c ON c.id = a.course_id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT a.student_id) >= 10;

Your task

Pinewood Play's content team ranks titles by how many viewers finish them. A viewer finished a title if any one of their views covered at least 90% of the runtime, and a viewer who rewatches still counts once.

For titles with at least 4 distinct viewers, return title, viewers (distinct profiles), completers (distinct profiles with a view where minutes_watched >= 90% of runtime_min) and completion_pct (completers as a % of viewers, rounded to 1 decimal). Sort by completion_pct descending, then title.

Starting Postgres in your browser…

Checking your access…