Find the top 5 artists whose songs appear most frequently in the Top 10 of the global_song_rank table. Output the artist name and their rank.
This multi-table ranking problem reflects Spotify's music analytics and chart generation systems. Determining which artists dominate the charts requires joining multiple tables, filtering by rank thresholds, aggregating counts, and then ranking the results—a complex pipeline that tests your ability to chain SQL operations logically. Spotify uses this to assess candidates' skills in building the analytical queries that power their 'Wrapped' features and artist insights.
Advanced concepts: multiple JOINs across dimension tables (artists, songs, rankings), WHERE filtering before aggregation, GROUP BY for artist-level aggregation, window functions (DENSE_RANK) for ranking aggregated results, and CTEs for query organization. Understanding DENSE_RANK vs RANK is crucial—DENSE_RANK ensures no gaps in rankings when there are ties.
Spotify's data teams use similar queries to: generate weekly chart rankings and 'Top Artists' features, create personalized year-end Wrapped summaries, identify trending artists for playlist curation, calculate royalty distributions based on play counts, power artist analytics dashboards showing chart performance, and feed recommendation algorithms with popularity signals.
When tackling this Spotify problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
WITH artist_counts AS (
SELECT
a.artist_name,
COUNT(s.song_id) AS appearance_count
FROM artists a
JOIN songs s ON a.artist_id = s.artist_id
JOIN global_song_rank g ON s.song_id = g.song_id
WHERE g.rank <= 10
GROUP BY a.artist_name
),
ranked_artists AS (
SELECT
artist_name,
DENSE_RANK() OVER (ORDER BY appearance_count DESC) AS artist_rank
FROM artist_counts
)
SELECT artist_name, artist_rank
FROM ranked_artists
WHERE artist_rank <= 5The first CTE aggregates Top 10 chart appearances per artist across all days. The second CTE applies DENSE_RANK ordered by appearance count descending, so tied artists share the same rank with no gaps. The outer query filters to artist_rank <= 5, which may return more than 5 rows when ties occur at rank 5 — this is correct per the problem statement. Two named CTEs keep each logical step isolated and independently debuggable, making this the most readable production-quality approach for a complex multi-table aggregation with ranking.
SELECT artist_name, artist_rank
FROM (
SELECT
a.artist_name,
DENSE_RANK() OVER (
ORDER BY COUNT(s.song_id) DESC
) AS artist_rank
FROM artists a
JOIN songs s ON a.artist_id = s.artist_id
JOIN global_song_rank g ON s.song_id = g.song_id
WHERE g.rank <= 10
GROUP BY a.artist_name
) ranked
WHERE artist_rank <= 5This compact version applies DENSE_RANK directly over the aggregated count inside a single subquery, eliminating the intermediate CTE. The window function is evaluated after GROUP BY completes, so COUNT(s.song_id) in the OVER clause references the already-aggregated per-artist value. This produces the same execution plan as the two-CTE version. Prefer this for brevity in interview settings or scripts; prefer the CTE version when artist_counts needs to be reused or when the query is embedded in a larger analytical pipeline that benefits from named intermediate steps.
The three-table join (artists → songs → global_song_rank) requires indexes on join columns: artists(artist_id), songs(artist_id, song_id), and global_song_rank(song_id, rank). The WHERE g.rank <= 10 filter is critical for performance — without an index on global_song_rank(rank), the database scans the entire rankings table. With a composite index on (rank, song_id), the optimizer uses an index range scan to retrieve only Top 10 rows before joining, dramatically reducing the intermediate result size. GROUP BY a.artist_name with many artists is memory-intensive; grouping by a.artist_id (integer) and joining artist_name in the outer SELECT is faster for string-heavy tables. DENSE_RANK is a window function applied post-aggregation and adds minimal overhead. The two-CTE and single-subquery versions produce identical query plans in most modern databases — the optimizer inlines non-recursive CTEs. For very large global_song_rank tables covering years of daily data, partitioning by day and filtering to a date range reduces scan cost by orders of magnitude.
Using RANK() instead of DENSE_RANK() is the most common mistake — RANK() leaves gaps after ties (e.g., 1, 2, 2, 4), potentially excluding artists who should qualify for the top 5 by count. DENSE_RANK() assigns consecutive ranks (1, 2, 2, 3), correctly including all tied artists. Attempting to filter WHERE artist_rank <= 5 before the window function is computed causes an error, since window functions cannot be referenced in WHERE — the filter must go in an outer query or QUALIFY clause (if supported). Grouping by a.artist_name instead of a.artist_id risks miscounting if two distinct artists share the same name. Forgetting WHERE g.rank <= 10 includes all chart positions, completely changing the meaning of 'top 5 artists in the Top 10.'
ROW_NUMBER() assigns unique sequential integers with no ties. RANK() gives tied rows the same number but skips subsequent ranks (1,2,2,4). DENSE_RANK() gives tied rows the same number without gaps (1,2,2,3). For top-N with ties included, DENSE_RANK() is correct — using RANK() here could skip rank 5 after a tie at rank 4, causing WHERE artist_rank <= 5 to exclude artists who legitimately belong in the top 5 by appearance count.
You can approximate it: find the minimum appearance count among the top 5 distinct counts using a subquery, then return all artists with count >= that threshold. However, handling ties cleanly is complex and the query becomes error-prone. DENSE_RANK() is purpose-built for this and far cleaner. In interviews, demonstrating window function knowledge is expected for Hard-difficulty problems and shows stronger SQL fluency.
DENSE_RANK() assigns the same rank to all tied artists. If three artists share the 5th highest appearance count, all receive artist_rank = 5 and all are returned by WHERE artist_rank <= 5, producing 7+ rows. This is intentional and correct per the problem. If the requirement were strictly 5 rows, you would use ROW_NUMBER() with a deterministic tie-breaker: ORDER BY appearance_count DESC, artist_name ASC.