Streaming Buffer Ratio
Netflix, You Tube
Problem Statement
To optimize streaming quality, Netflix tracks the ratio of buffering events to successful play events. A high ratio indicates a poor user experience in a specific time window.
Write a query to calculate the Buffer Ratio for every user in 30-minute intervals.
Rules:
- The windows are fixed: 12:00:00-12:30:00, 12:30:00-13:00:00, etc.
- session_window_start must be a timestamp without time zone.
- Buffer Ratio = (Count of buffering events) / (Count of playback events).
- Only include windows where there is at least 1 playback event.
- Output columns: user_id, session_window_start, buffer_ratio.
- Result must be sorted by session_window_start ascending, then buffer_ratio descending.
Tests your understanding of
Window Functions, Time Series and Aggregation
Input Tables
playback_events
| event_id(INTEGER) | user_id(INTEGER) | event_type(VARCHAR) | event_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | 1 | playback | 2024-01-01 12:05:00 |
| 2 | 1 | buffering | 2024-01-01 12:10:00 |
| 3 | 1 | playback | 2024-01-01 12:15:00 |
| 4 | 2 | playback | 2024-01-01 12:40:00 |
| 5 | 2 | buffering | 2024-01-01 12:45:00 |
| 6 | 2 | buffering | 2024-01-01 12:50:00 |
| 7 | 3 | playback | 2024-01-01 13:00:00 |
| 8 | 1 | playback | 2024-01-01 12:35:00 |
Expected Output
| user_id(INTEGER) | session_window_start(TIMESTAMP) | buffer_ratio(DECIMAL) |
|---|---|---|
| 1 | 2024-01-01 12:00:00 | 0.5 |
| 2 | 2024-01-01 12:30:00 | 2 |
| 1 | 2024-01-01 12:30:00 | 0 |
| 3 | 2024-01-01 13:00:00 | 0 |
Tags
HardWindow FunctionsTime SeriesAggregation
35-45 min
38%