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_name and artist contain only alphanumeric characters and spaces
  • 1 ≤ track_name.length, artist.length ≤ 50

Visualization

Tap to expand
Count Artist Occurrences On Spotify Ranking List INPUT Spotify Table track_id track_name artist 1 Song A Drake 2 Song B Taylor 3 Song C Drake 4 Song D Ed 5 Song E Drake 6 Song F Taylor Table with track info including artist names SELECT artist, COUNT(*) AS cnt FROM Spotify GROUP BY artist ORDER BY cnt DESC, artist ALGORITHM STEPS 1 GROUP BY artist Group all rows by artist name Drake(3) Taylor(2) Ed(1) 2 COUNT(*) Count rows in each group artist cnt Drake 3 Taylor 2 Ed 1 3 ORDER BY cnt DESC Sort by count descending 4 Tiebreaker: artist ASC If counts equal, sort by artist name alphabetically 3 --> 2 --> 1 (by count) A --> B --> C (if tied) FINAL RESULT Output Table artist occurrence_cnt Drake 3 Taylor 2 Ed 1 OK Artists ordered by occurrence count (DESC) 1st Drake 2nd Taylor 3rd Ed Key Insight: GROUP BY with COUNT(*) is the standard SQL pattern for counting occurrences. The ORDER BY clause with multiple columns handles both primary sorting (by count DESC) and tiebreaking (by artist ASC). Time Complexity: O(n log n) where n is the number of distinct artists for sorting. TutorialsPoint - Count Artist Occurrences On Spotify Ranking List | Optimal Solution

Related Problems

Asked in
Spotify 15 Apple 12 Amazon 8
28.4K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen