Problem

Table: Spotify

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| track_name  | varchar |
| artist      | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row contains an id, track_name, and artist.

Write a solution to find how many times each artist appeared on the Spotify ranking list.

Return the result table having the artist’s name along with the corresponding number of occurrences ordered by occurrence count in descending order. If the occurrences are equal, then it’s ordered by the artist’s name in ascending order.

The result format is in the following example​​​​​.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

Input: Spotify table: 
+---------+--------------------+------------+ 
| id      | track_name         | artist     |  
+---------+--------------------+------------+
| 303651  | Heart Won't Forget | Sia        |
| 1046089 | Shape of you       | Ed Sheeran |
| 33445   | I'm the one        | DJ Khalid  |
| 811266  | Young Dumb & Broke | DJ Khalid  | 
| 505727  | Happier            | Ed Sheeran |
+---------+--------------------+------------+ 
Output: +------------+-------------+
| artist     | occurrences | 
+------------+-------------+
| DJ Khalid  | 2           |
| Ed Sheeran | 2           |
| Sia        | 1           | 
+------------+-------------+ 
Explanation: The count of occurrences is listed in descending order under the column name "occurrences". If the number of occurrences is the same, the artist's names are sorted in ascending order.

Solution

Method 1 – SQL Group By and Count 1

Intuition

To count how many times each artist appears, we group the Spotify table by artist and count the number of rows for each artist. We then order the results by the count in descending order, and by artist name in ascending order for ties.

Approach

  1. Group the Spotify table by artist.
  2. Count the number of occurrences for each artist.
  3. Order the result by occurrence count descending, then by artist name ascending.

Code

1
2
3
4

SELECT artist, COUNT(*) AS occurrences
FROM Spotify
GROUP BY artist
ORDER BY occurrences DESC, artist ASC;
1
2
3
4

SELECT artist, COUNT(*) AS occurrences
FROM Spotify
GROUP BY artist
ORDER BY occurrences DESC, artist ASC;
1
2
3

def count_artist_occurrences(spotify: pd.DataFrame) -> pd.DataFrame:
    df = spotify.groupby('artist').size().reset_index(name='occurrences')
    return df.sort_values(['occurrences', 'artist'], ascending=[False, True])

Complexity

  • ⏰ Time complexity: O(n log n), where n is the number of rows in the Spotify table, due to the group by and sort operations.
  • 🧺 Space complexity: O(n), for storing the grouped and sorted results.