Katkat
Back

Calculate Playback Counts per Stream

AdiaData Scientist · Technical Screen
EasyData ScientistTechnical ScreenSoftware Engineering Fundamentals

Last updated

This question tests the ability to perform relational database joins and use aggregate functions (COUNT) with grouping to summarize event-level data.

Problem Statement

You are given two tables representing playback events on a media platform. Your task is to calculate the total number of playback events for each stream and include the corresponding streamer's name.

Tables

  1. streams table:

    • stream_id (int): Unique identifier for a stream.
    • streamer_name (varchar): Name of the streamer.

  2. playback_events table:

    • event_id (int): Unique identifier for the event.
    • stream_id (int): ID of the stream where the event occurred.

Task

Write a SQL query that returns the stream_id, streamer_name, and the total count of playback events for each stream. If a stream has zero events, it should still appear in the list with a count of 0.

Example Input

streams:

stream_idstreamer_name
101Alice
102Bob

playback_events:

event_idstream_id
1101
2101
3102

Example Output

stream_idstreamer_nameevent_count
101Alice2
102Bob1

Constraints

  • stream_id is the primary key in streams.
  • playback_events can contain multiple events per stream_id.
  • Ensure all streams are included, even if they have no corresponding events in the playback_events table.

Hints

Hint 1

Use a JOIN clause to combine the two tables based on the common 'stream_id' column.

Hint 2

To include streams with zero events, consider using a LEFT JOIN starting from the 'streams' table.

Hint 3

Use the GROUP BY clause on 'stream_id' and 'streamer_name' and use the COUNT() function to tally events.

Follow-up questions

  • How would you modify the query to only return streams that have more than 100 playback events?
  • If we wanted to calculate the average number of events per streamer, how would you approach that using a subquery or a CTE?
  • What would be the performance impact of adding an index on the 'stream_id' column in both tables?