9 Spotify SQL Interview Questions (Updated 2025)

At Spotify, SQL is used across the company for analyzing user listening behaviors for custom playlist creation and managing databases to optimize server performance during peak streaming hours. Unsurprisingly this is why Spotify asks SQL query questions during interviews for Data Analytics, Data Science, and Data Engineering jobs.

In case you're preparing for a SQL Assessment, here’s 9 Spotify SQL interview questions to practice, which are similar to recently asked questions at Spotify – can you solve them?

9 Spotify SQL Interview Questions

Sure, here is a SQL interview question potentially suitable for Spotify:

SQL Question 1: Top 5 Artists

Assume there are three Spotify tables: artists, songs, and global_song_rank, which contain information about the artists, songs, and music charts, respectively.

Write a query to find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table. Display the top 5 artist names in ascending order, along with their song appearance ranking.

If two or more artists have the same number of song appearances, they should be assigned the same ranking, and the rank numbers should be continuous (i.e. 1, 2, 2, 3, 4, 5). If you've never seen a rank order like this before, do the rank window function tutorial.

artists Table:

artists Example Input:

songs Table:

songs Example Input:

global_song_rank Table:

global_song_rank Example Input:

Example Output:

Answer:

WITH top_10_cte AS (
  SELECT 
    artists.artist_name,
    DENSE_RANK() OVER (
      ORDER BY COUNT(songs.song_id) DESC) AS artist_rank
  FROM artists
  INNER JOIN songs
    ON artists.artist_id = songs.artist_id
  INNER JOIN global_song_rank AS ranking
    ON songs.song_id = ranking.song_id
  WHERE ranking.rank <= 10
  GROUP BY artists.artist_name
)

SELECT artist_name, artist_rank
FROM top_10_cte
WHERE artist_rank <= 5;

To try this question out for yourself for FREE go here, Spotify SQL Interview Question.

SQL Question 2: Analyze Artist Popularity Over Time

Question Description: Let's assume you are a Data Analyst at Spotify. You are given a data table named artist_listens containing daily listening counts for different artists. The table has three columns: artist_id, listen_date, and daily_listens.

You are required to write a SQL query to calculate the 7-day rolling average of daily listens for each artist. The rolling average should be calculated for each day for each artist based on the previous 7 days (including the current day).

artist_listens Example Input:

Notice that the listen_date column is date formatted.

Example Output:

Please pay attention to the rounding in the result.

Answer:

SELECT 
artist_id, 
listen_date, 
AVG(daily_listens) OVER (
  PARTITION BY artist_id 
  ORDER BY listen_date 
  RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_avg_listens
FROM artist_listens
ORDER BY artist_id, listen_date;

Explanation:

This SQL query computes the rolling average by using a window function AVG with a window frame defined as "the previous 6 days plus the current row".

The PARTITION BY clause ensures the rolling average is calculated separately for each artist.

The ORDER BY clause is used to order the rows in each partition by the listen_date.

The RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW clause defines the window frame for the window function. It states that for a given row, consider all rows from 6 days before to the current row.

Finally, the AVG() function calculates the average of daily_listens over the defined window frame.

The ORDER BY artist_id, listen_date; at the end just keeps the result set ordered by artist and date.

To solve another question about calculating rates, try this TikTok SQL question on DataLemur's interactive coding environment:

SQL Question 3: Spotify Streaming History

You're given two tables containing data on Spotify users' streaming activity: songs_history which has historical streaming data, and songs_weekly which has data from the current week.

Write a query that outputs the user ID, song ID, and cumulative count of song plays up to August 4th, 2022, sorted in descending order.

Assume that there may be new users or songs in the songs_weekly table that are not present in the songs_history table.

songs_history Table:

songs_history Example Input:

songs_weekly Table:

songs_weekly Example Input:

Example Output:

Answer:

WITH history AS (
  SELECT 
    user_id, 
    song_id, 
    song_plays
  FROM songs_history

  UNION ALL

  SELECT 
    user_id, 
    song_id, 
    COUNT(song_id) AS song_plays
  FROM songs_weekly
  WHERE listen_time <= '08/04/2022 23:59:59'
  GROUP BY user_id, song_id
)

SELECT 
  user_id, 
  song_id, 
  SUM(song_plays) AS song_count
FROM history
GROUP BY 
  user_id, 
  song_id
ORDER BY song_count DESC;

To see an additional solution go to DataLemur and solve this Spotify SQL Interview Question.

SQL Question 4: Music Streaming Statistics

As a Data Analyst of Spotify, suppose your team is interested in understanding the listening habits of the users. You're provided with the following tables:

1. users table contains information about users.
2. songs table contains information about songs.
3. artists table contains information about song artists.
4. streaming table logs every song listened to by each user.

The following relationships hold:

• Every song has one and only one artist, but an artist can have multiple songs.
• Every song can be listened to by multiple users, and every user can listen to multiple songs.

Your goal is to write a SQL query that returns each user's favourite artist, based on the number of songs they've listened to by the artist.

users Example Input:

songs Example Input:

artists Example Input:

streaming Example Input:

Answer:

SELECT u.username, a.artist_name FROM (
    SELECT stream.user_id, songs.artist_id, count(*) as num_songs
    FROM streaming AS stream
    JOIN songs ON stream.song_id = songs.song_id
    GROUP BY stream.user_id, songs.artist_id
    ORDER BY num_songs DESC ) AS sub_query
JOIN users AS u ON u.user_id = sub_query.user_id
JOIN artists AS a ON a.artist_id = sub_query.artist_id
LIMIT 1;

The above query will find the count of songs each user has listened to by each artist, grouping by both user_id and artist_id. This result is sorted in descending order of the count, and the top record for each user represents their favorite artist. The outer query then joins this result back to the artists and users tables to get the respective names.

To practice a related SQL problem on DataLemur's free online SQL coding environment, attempt this Facebook SQL Interview question:

SQL Question 5: What is denormalization?

Denormalization is a technique used to improve the read performance of a database, typically at the expense of some write performance.

By adding redundant copies of data or grouping data together in a way that does not follow normalization rules, denormalization improves the performance and scalability of a database by eliminating costly join operations, which is important for OLAP use cases that are read-heavy and have minimal updates/inserts.

SQL Question 6: Filter Spotify Users Based on Subscription and Activity

As a data analyst at Spotify, you are tasked with extracting a list of active Premium subscribers who have listened to at least 15 different artists in the current month. Active users are those who have logged in within the last 30 days.

Assuming you have two tables:

users Example Input:

|**user_id**|**subscription_status**|**last_login**|
|:----|:----|:----|
|1|Premium|2022-08-20|
|2|Free|2022-08-01|
|3|Premium|2022-07-30|
|4|Premium|2022-08-21|

activity Example Input:

|**user_id**|**artist_name**|**month**|
|:----|:----|:----|
|1|Artist 1|August|
|1|Artist 2|August|
|1|Artist 3|August|
|2|Artist 4|August|
|1|Artist 5|August|
|1|Artist 6|August|
|1|Artist 7|August|
|1|Artist 8|August|
|1|Artist 9|August|
|1|Artist 10|August|
|1|Artist 11|August|
|1|Artist 12|August|
|1|Artist 13|August|
|1|Artist 14|August|
|1|Artist 15|August|
|3|Artist 1|July|
|4|Artist 2|August|

Answer:

    SELECT u.user_id FROM users u
    INNER JOIN (
        SELECT user_id, COUNT(DISTINCT artist_name) as cnt 
        FROM activity 
        WHERE month = 'August'
        GROUP BY user_id
    ) a
    ON u.user_id = a.user_id 
    WHERE u.subscription_status = 'Premium'
    AND u.last_login >= current_date - interval '30 days'
    AND a.cnt >= 15;

This query first groups the activity table by user_id and calculates the count of distinct artists each user interacted with in August. It then joins this table with the users table on user_id. The WHERE clause filters for Premium users who have logged in within the last 30 days and have interacted with at least 15 different artists.

To solve a similar problem about calculating rates, solve this SQL interview question from TikTok within DataLemur's interactive SQL code editor:

SQL Question 7: What distinguishes a left join from a right join?

"In SQL, a join generally retrieves rows from multiple tables and combines them into a single result set. For an example of the difference between a left vs. right join, suppose you had a table of Spotify orders and Spotify customers.

A LEFT JOIN retrieves all rows from the left table (in this case, the Orders table) and any matching rows from the right table (the Customers table). If there is no match in the right table, NULL values will be returned for the right table's columns.

A RIGHT JOIN combines all rows from the right table (in this case, the Customers table) and any matching rows from the left table (the Orders table). If there is no match in the left table, NULL values will be displayed for the left table's columns.

SQL Question 8: Calculate the average listening duration for each music genre on Spotify

Suppose that Spotify would like to understand better the average listening duration for each genre of music on their platform. As a data scientist, your task is to write a SQL query that calculates the average listening duration per genre for every month.

Assume you have access to a user_activity table and a songs table with the following schema:

user_activity Example Input:

songs Example Input:

Your aim is to produce a table like:

Example Output:

Answer:

In order to get to the answer, we need to join the two tables on song_id and then use the GROUP BY clause. The AVG() function can be used with GROUP BY to find the average listening duration for each music genre.

SELECT EXTRACT(MONTH FROM ua.timestamp) AS mth,
       s.genre,
       AVG(ua.listening_duration_sec)
FROM user_activity ua
JOIN songs s ON ua.song_id = s.song_id
GROUP BY mth, s.genre;

This SQL query does the following:

• Joins the user_activity table (as ua) and the songs table (as s) on the song_id field.
• Uses the EXTRACT function to get the month from the timestamp in user_activity.
• Groups by month and genre.
• Uses AVG to calculate the average listening_duration_sec for each genre in each month. The result will be average listening duration per genre for every month.

To solve another question about calculating rates, solve this SQL interview question from TikTok within DataLemur's interactive coding environment:

SQL Question 9: Find Users Who've Listened to All Albums of a Specific Artist

As an analyst at Spotify, you're tasked to identify all users who have listened to all albums of the artist "Adele". Assume you have access to a users table that keeps track of user information and an album_listens table that keeps track of all instances where a user listened to an album. Here are the table structures:

users table

album_listens table

Using SQL, write a query to find all users who have listened to all Adele's albums (19, 21, 25).

Answer:

SELECT u.user_id, u.user_name
FROM users u
WHERE NOT EXISTS (SELECT 1
                  FROM (SELECT DISTINCT album_name 
                        FROM album_listens 
                        WHERE artist_name = 'Adele') a
                  WHERE NOT EXISTS (SELECT 1 
                                    FROM album_listens al
                                    WHERE al.user_id = u.user_id 
                                    AND al.album_name = a.album_name
                                    AND al.artist_name = 'Adele'))

This query works by first identifying all distinct Adele's albums and then checking whether there are any of these albums that a certain user has not listened to. If a user has listened to all Adele's albums, they will not have any of Adele's albums that they have not listened to. Such users are selected by the query.

To solve a related SQL interview question on DataLemur's free interactive SQL code editor, solve this Meta SQL interview question:

How To Prepare for the Spotify SQL Interview

The key to acing a Spotify SQL interview is to practice, practice, and then practice some more! In addition to solving the above Spotify SQL interview questions, you should also solve the 200+ tricky sql questions on DataLemur which come from companies like Facebook, Google and unicorn tech startups.

Each exercise has multiple hints, detailed solutions and best of all, there is an interactive coding environment so you can instantly run your SQL query and have it executed.

To prep for the Spotify SQL interview you can also be useful to practice SQL problems from other tech companies like:

• TikTok SQL questions

• Apple SQL questions

• Google SQL questions

• Amazon SQL questions

But if your SQL query skills are weak, forget about going right into solving questions – improve your SQL foundations with this DataLemur SQL Tutorial.

This tutorial covers SQL topics like joining a table to itself and window functions – both of which pop up routinely in Spotify interviews.

Spotify Data Science Interview Tips

What Do Spotify Data Science Interviews Cover?

For the Spotify Data Science Interview, besides SQL questions, the other types of questions to practice:

• Probability & Stats Questions
• Python or R Programming Questions
• Business Sense and Product-Sense Questions
• Machine Learning and Predictive Modeling Questions
• Behavioral & Resume-Based Questions

How To Prepare for Spotify Data Science Interviews?

The best way to prepare for Spotify Data Science interviews is by reading Ace the Data Science Interview. The book's got:

• 201 Interview Questions from companies like Microsoft, Google & Amazon
• A Crash Course on SQL, Product-Sense & ML
• Amazing Reviews (900+ reviews, 4.5-star rating)