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…