Calculate Playback Counts per Stream
Company: Adia
Role: Data Scientist
Category: Software Engineering Fundamentals
Difficulty: easy
Interview Round: Technical Screen
You are given two database tables:
`streamer`
| column | type | description |
|---|---|---|
| `streamer_id` | integer | Primary key of the streamer |
| `streamer_name` | string | Display name of the streamer |
`streaming`
| 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 |
Each row in `streaming` represents one play event. Write a SQL query that returns the number of plays for every stream of every streamer.
The output should include:
- `streamer_id`
- `streamer_name`
- `stream_id`
- `play_count`
Order the result by `streamer_id` and then `stream_id`.
Quick Answer: This question evaluates SQL skills in aggregation and joins, specifically counting event occurrences and relating them to stream and streamer identifiers.