Search ⌘K
Spotify
Spotify

Interview Core Track · Medium · 25 min

1. Identify Spotify's Most Frequent Listeners

Spotify wants to identify their 'whale users', these are users who listen to the most tracks every month. They are potential customers to involve in user fee...

Interview Core Track
Medium
25 min
joins
aggregation
filtering

Company labels are directional practice context, not official interview guidance.

Timer 00:00
Back to practice

Objective

Practice joins through a Spotify-tagged business scenario.

Approach

Use this track to improve speed, edge-case handling, and accuracy under timed conditions.

Company context

Company labels are directional practice context, not official interview guidance.

Prereq: query basics
Prereq: filtering

Spotify wants to identify their 'whale users', these are users who listen to the most tracks every month. They are potential customers to involve in user feedback sessions. Given the database tables users and user_listen_history, write a SQL query to identify the top 5 users who have listened to the most unique tracks in the last 30 days. Assume today's date is 2023-03-22. users **Example Input:** user_id username sign_up_date email ---------------------------------------- 1001 user1 10/02/2021 user1@gmail.com 2002 user2 22/05/2022 user2@yahoo.com 3003 user3 01/01/2022 user3@hotmail.com 4004 user4 15/07/2021 user4@aol.com 5005 user5 24/12/2021 user5@msn.com user_listen_history **Example Input:** listen_id user_id listen_date track_id -------------------------------------------- 1 1001 02/03/2023 100 2 1001 02/03/2023 101 3 1001 03/03/2023 100 4 2002 03/03/2023 103 5 2002 03/03/2023 104 5 3003 03/03/2023 100 6 4004 03/03/2023 104 7 5005 03/03/2023 100 **Answer:** SELECT user_id, username, COUNT(DISTINCT track_id) as total_unique_tracks_listened FROM users u JOIN user_listen_history ulh USING(user_id) WHERE listen_date BETWEEN '2023-02-22' AND '2023-03-22' GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 5; ---

Pro question
This is a Pro question

Upgrade to Pro to unlock this prompt, the SQL workspace, and all 254 problems.

Upgrade to Pro — unlock all 254 problems
Editor
SQL workspace

Run queries against the protected question data, then submit once the result shape looks right.

Sign in to run SQL

Create a free account or sign in before running queries against protected question data.