Count Artist Occurrences On Spotify Ranking List - Problem
Given a table Spotify that contains information about tracks on Spotify's ranking list, write a SQL query to find how many times each artist appeared on the ranking list.
Requirements:
- Return the artist's name along with their occurrence count
- Order by occurrence count in descending order
- If occurrence counts are equal, order by artist name in ascending order
Table Schema
Spotify
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Primary key, unique identifier for each track |
track_name
|
varchar | Name of the track |
artist
|
varchar | Name of the artist who performed the track |
Primary Key: id
Note: Each row represents a unique track on Spotify's ranking list
Input & Output
Example 1 — Multiple Artists with Different Counts
Input Table:
| id | track_name | artist |
|---|---|---|
| 1 | Shape of You | Ed Sheeran |
| 2 | Blinding Lights | The Weeknd |
| 3 | Perfect | Ed Sheeran |
| 4 | Bad Habits | Ed Sheeran |
| 5 | Starboy | The Weeknd |
Output:
| artist | occurrences |
|---|---|
| Ed Sheeran | 3 |
| The Weeknd | 2 |
💡 Note:
Ed Sheeran appears 3 times (Shape of You, Perfect, Bad Habits) and The Weeknd appears 2 times (Blinding Lights, Starboy). Results are ordered by occurrence count in descending order.
Example 2 — Tied Counts with Alphabetical Ordering
Input Table:
| id | track_name | artist |
|---|---|---|
| 1 | Song A | Zara |
| 2 | Song B | Alice |
| 3 | Song C | Bob |
| 4 | Song D | Alice |
Output:
| artist | occurrences |
|---|---|
| Alice | 2 |
| Bob | 1 |
| Zara | 1 |
💡 Note:
Alice appears 2 times, while Bob and Zara each appear once. Since Bob and Zara have the same count, they are ordered alphabetically (Bob before Zara).
Example 3 — Single Artist Multiple Times
Input Table:
| id | track_name | artist |
|---|---|---|
| 1 | Hit Song 1 | Taylor Swift |
| 2 | Hit Song 2 | Taylor Swift |
| 3 | Hit Song 3 | Taylor Swift |
Output:
| artist | occurrences |
|---|---|
| Taylor Swift | 3 |
💡 Note:
Taylor Swift appears 3 times on the ranking list with different tracks, resulting in a single row with count 3.
Constraints
-
1 ≤ id ≤ 1000 -
track_nameandartistcontain only alphanumeric characters and spaces -
1 ≤ track_name.length, artist.length ≤ 50
Visualization
Tap to expand
Understanding the Visualization
1
Input
Spotify table with tracks and artists
2
GROUP BY
Group tracks by artist and count
3
Output
Artist names with occurrence counts
Key Takeaway
🎯 Key Insight: GROUP BY with COUNT(*) efficiently aggregates and counts occurrences per group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code