Watch 2 video solutions for Count Artist Occurrences On Spotify Ranking List, a easy level problem involving Database. This walkthrough by Everyday Data Science has 530 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
Example 1:
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.
Problem Overview: You are given a Spotify ranking table containing artists that appear in the ranking list. The task is to count how many times each artist appears and return the total occurrences per artist. The result typically needs to be grouped by artist and ordered based on the required conditions.
Approach 1: GROUP BY Aggregation (O(n) time, O(k) space)
The most direct solution uses SQL aggregation. Iterate through all rows in the ranking table and group them by artist. For each group, apply COUNT(*) to calculate how many times the artist appears in the list. SQL engines internally scan the table once and maintain aggregated counters for each unique artist. This approach works well because counting grouped records is exactly what SQL aggregation functions are designed for. Space complexity depends on the number of unique artists k.
Approach 2: Subquery with Aggregation (O(n) time, O(k) space)
Another option computes counts inside a subquery and then selects from that derived table. The inner query groups rows by artist and calculates the occurrence count using COUNT(). The outer query simply formats or orders the results. This pattern is useful when you want to apply additional filtering or joins after computing the aggregation. The database still performs a single scan of the ranking list, so the time complexity remains O(n). The extra logical layer mainly improves readability for more complex queries.
Approach 3: Window Function Counting (O(n) time, O(n) space)
You can also compute artist frequencies using a window function like COUNT(*) OVER (PARTITION BY artist). This calculates the number of occurrences for each artist without collapsing rows immediately. Afterward, you can select distinct artists and their computed counts. Window functions are powerful when the same aggregated value must coexist with row-level data. This technique appears frequently in advanced database queries and ranking analytics.
Recommended for interviews: The GROUP BY aggregation approach is the expected solution. It shows that you understand how relational databases perform counting and grouping operations efficiently. While window functions demonstrate deeper SQL knowledge, the grouped aggregation is simpler, clearer, and exactly matches the requirement of counting artist appearances in the ranking list. Understanding both patterns strengthens your grasp of GROUP BY queries and analytical SQL.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY Aggregation | O(n) | O(k) | Best general solution for counting occurrences per artist |
| Subquery with Aggregation | O(n) | O(k) | Useful when additional filtering or joins are required after aggregation |
| Window Function Counting | O(n) | O(n) | When you need aggregated counts alongside row-level ranking data |