Join Tables and Rank Users
Company: Spotify
Role: Machine Learning Engineer
Category: Coding & Algorithms
Difficulty: medium
Interview Round: Onsite
You are given two analytics tables:
- `users(user_id, country, signup_date)`
- `listening_events(event_id, user_id, played_at, minutes_played)`
Because the data pipeline may retry writes, `listening_events` can contain duplicate rows with the same `event_id`.
Write a SQL query to return, for each country, the top 3 users by total `minutes_played` in the last 30 days. For each returned row, include `country`, `user_id`, `total_minutes`, and the user's rank within the country.
Then discuss:
1. How you would deduplicate events before aggregation.
2. How you would optimize the query if `listening_events` is extremely large.
3. When partitioning versus sharding would help.
Overview: This question evaluates proficiency in SQL and data-engineering skills—specifically joins, aggregation and ranking via window functions, event deduplication, and query performance considerations across analytics tables.