Calculate Playback Counts per Stream
Problem Description
You are provided with two database tables containing information about streamers and their streaming events:
streamer Table
This table stores details about individual streamers.
| column | type | description |
|---|---|---|
streamer_id | integer | Primary key of the streamer |
streamer_name | string | Display name of the streamer |
streaming Table
This table records individual play events for streams. Each row signifies one instance of a viewer playing a stream.
| column | type | description |
|---|---|---|
stream_id | integer | Identifier of a livestream or video stream |
streamer_id | integer | The streamer who owns the stream |
viewer_id | integer | Viewer who played or watched the stream |
played_at | timestamp | Time of the play event |
Task
Write a SQL query that calculates the total number of play events for every unique stream associated with each streamer.
Required Output
The result set should include the following columns:
streamer_idstreamer_namestream_idplay_count
Ordering
Order the final result first by streamer_id in ascending order, and then by stream_id in ascending order.
AI interview prep
Core dilemma
Efficiently and accurately aggregating event data across related entities in a relational database, considering potential scale and performance implications.
3 tradeoffs and 3 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Calculate Playback Counts per Stream
Problem Description
You are provided with two database tables containing information about streamers and their streaming events:
streamer Table
This table stores details about individual streamers.
| column | type | description |
|---|---|---|
streamer_id | integer | Primary key of the streamer |
streamer_name | string | Display name of the streamer |
streaming Table
This table records individual play events for streams. Each row signifies one instance of a viewer playing a stream.
| column | type | description |
|---|---|---|
stream_id | integer | Identifier of a livestream or video stream |
streamer_id | integer | The streamer who owns the stream |
viewer_id | integer | Viewer who played or watched the stream |
played_at | timestamp | Time of the play event |
Task
Write a SQL query that calculates the total number of play events for every unique stream associated with each streamer.
Required Output
The result set should include the following columns:
streamer_idstreamer_namestream_idplay_count
Ordering
Order the final result first by streamer_id in ascending order, and then by stream_id in ascending order.