Video View Funnel
ASKED IN INTERVIEWTik Tok
Problem Statement
TikTok creators need to understand where their audience drops off. We track three specific events in the video player:
- started: The user clicked play.
- half_watched: The user reached 50% of the video duration.
- fully_watched: The user reached 100% of the video.
Write an SQL query to calculate the overall conversion rates for the entire platform.
Rules:
- Calculate two metrics:
- half_rate: (Users who reached half_watched) / (Users who started)
- full_rate: (Users who reached fully_watched) / (Users who started)
- Use unique user counts for each stage.
- Results should be rounded to 2 decimal places.
- Output columns: half_rate, full_rate.
- If no videos were started, return 0.00 for both rates.
Tests your understanding of
Funnel Analysis, Aggregation and CASE WHEN
Input Tables
Events
| event_id(INTEGER) | user_id(INTEGER) | video_id(INTEGER) | event_type(VARCHAR) | timestamp(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 1 | 10 | started | 2026-02-10 10:00:00 |
| 2 | 1 | 10 | half_watched | 2026-02-10 10:00:15 |
| 3 | 1 | 10 | fully_watched | 2026-02-10 10:00:30 |
| 4 | 2 | 10 | started | 2026-02-10 10:01:00 |
| 5 | 2 | 10 | half_watched | 2026-02-10 10:01:15 |
| 6 | 3 | 11 | started | 2026-02-10 10:02:00 |
Videos
| video_id(INTEGER) | creator_id(INTEGER) | duration(INTEGER) |
|---|---|---|
| 10 | 50 | 30 |
| 11 | 51 | 60 |
| 12 | 52 | 15 |
| 13 | 53 | 45 |
| 14 | 54 | 20 |
| 15 | 55 | 120 |
Users
| user_id(INTEGER) | region(VARCHAR) |
|---|---|
| 1 | US |
| 2 | UK |
| 3 | IN |
| 4 | BR |
| 5 | JP |
| 6 | DE |
Expected Output
| half_rate(DECIMAL) | full_rate(DECIMAL) |
|---|---|
| 0.67 | 0.33 |
Tags
MediumASKED IN INTERVIEWFunnel AnalysisAggregationCASE WHEN
15-20 min
62%