Katkat
Back

Query Top Played Tracks Globally And By Country

SpotifyMachine Learning Engineer · Technical Screen
EasyMachine Learning EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

Tests writing clean SQL queries with aggregations, window functions, and deterministic tie-breaking to extract top playback metrics globally and per country.

Music Streaming Analytics: Top Tracks

As a Machine Learning Engineer on a music recommendation team, you need to extract popular tracks from streaming event logs to feed popularity-based candidate generators and editorial dashboards.

You are given a table playback_events that logs user listening activity:

playback_events (
    event_id        BIGINT PRIMARY KEY,
    user_id         BIGINT,
    track_id        BIGINT,
    country_code    VARCHAR(2),
    played_at       TIMESTAMP,
    duration_secs   INT
)

A track play is valid if duration_secs >= 30.

Task

Write two separate SQL queries for playback events recorded between 2024-01-01 00:00:00 and 2024-01-31 23:59:59 (inclusive):

  1. Global Top 5: Return the top 5 tracks globally by total valid play count. The output must include track_id and play_count, ordered by play_count descending, then track_id ascending.
  2. Country Top 3: Return the top 3 tracks for every country by total valid play count. Each country must have at most 3 rows. The output must include country_code, track_id, play_count, and rank (from 1 to 3), ordered by country_code ascending, then rank ascending.

Requirements & Edge Cases

  • Filter out any play where duration_secs < 30.
  • Break ties deterministically: if two tracks have the same play count, the track with the lower track_id takes precedence.
  • If a country has fewer than 3 unique played tracks, return all available tracks for that country.

Example Output

Global Top 5:

+----------+------------+
| track_id | play_count |
+----------+------------+
| 1042     | 54200      |
| 2011     | 48100      |
| 105      | 48100      |
| 9901     | 39050      |
| 451      | 31200      |
+----------+------------+

(Note: Track 105 and 2011 are tied at 48100; track 105 ranks ahead due to lower track_id.)

Country Top 3:

+--------------+----------+------------+------+
| country_code | track_id | play_count | rank |
+--------------+----------+------------+------+
| BR           | 884      | 12000      | 1    |
| BR           | 1042     | 11500      | 2    |
| BR           | 503      | 9200       | 3    |
| SE           | 1042     | 8100       | 1    |
| SE           | 772      | 4300       | 2    |
+--------------+----------+------------+------+

Hints

Hint 1

Pre-filter the raw logs on `duration_secs` and the date range before performing any aggregation.

Hint 2

For the country query, aggregate play counts by `(country_code, track_id)` first in a CTE before applying a window function.

Hint 3

Use `ROW_NUMBER() OVER (PARTITION BY country_code ORDER BY play_count DESC, track_id ASC)` to guarantee an exact, deterministic top 3 per country.

Follow-up questions

  • How does using ROW_NUMBER() differ from RANK() or DENSE_RANK() when handling ties, and which would you choose if product specifications required showing all tied tracks?
  • If playback_events contains 10 billion rows, how would you optimize data layout (partitioning, clustering, pre-aggregation) to make daily top-track queries fast?
  • How would you adapt this logic to compute a rolling 7-day top tracks window refreshed on an hourly basis?