Count Artist Occurrences On Spotify Ranking List

Count Artist Occurrences On Spotify Ranking List - Problem

Database Easy

Count Artist Occurrences On Spotify Ranking List

You're working with Spotify's music ranking database and need to analyze artist popularity! 🎵

Given a table called Spotify with columns id, track_name, and artist, your task is to count how many times each artist appears in the ranking list.

Goal: Return a result showing each artist's name along with their occurrence count, ordered by:
1. Occurrence count in descending order (most popular first)
2. If counts are equal, order by artist name in ascending order (alphabetical)

Table Schema:
Spotify
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| track_name | varchar |
| artist | varchar |
+-------------+---------+

This is a classic SQL aggregation problem that tests your understanding of GROUP BY, COUNT, and ORDER BY clauses.

Input & Output

example_1.sql — Basic Spotify Rankings

› Output: +-----------+------------------+ | artist | occurrence_count | +-----------+------------------+ | Ed Sheeran| 3 | | Bieber | 1 | | Dua Lipa | 1 | | Weeknd | 1 | +-----------+------------------+

💡 Note: Ed Sheeran appears 3 times (most frequent), so he's first. The other artists each appear once, so they're sorted alphabetically: Bieber, Dua Lipa, Weeknd.

example_2.sql — Equal Occurrences

› Output: +-----------+------------------+ | artist | occurrence_count | +-----------+------------------+ | Adele | 2 | | Beyonce | 2 | | Taylor | 2 | +-----------+------------------+

💡 Note: All artists have the same count (2), so they're ordered alphabetically: Adele, Beyonce, Taylor.

example_3.sql — Single Artist

$ Input: Spotify table: +----+------------+---------+ | id | track_name | artist | +----+------------+---------+ | 1 | Hit Song | Drake | +----+------------+---------+

› Output: +-----------+------------------+ | artist | occurrence_count | +-----------+------------------+ | Drake | 1 | +-----------+------------------+

💡 Note: Only one artist in the table, so the result contains just Drake with count 1.

Visualization

Tap to expand

Understanding the Visualization

Scan Table Once

SQL reads each row and groups by artist name

Count Groups

COUNT(*) tallies rows in each artist group

Sort Results

ORDER BY arranges by count (desc) then name (asc)

Key Takeaway

🎯 Key Insight: SQL's GROUP BY automatically handles the grouping and counting in a single efficient pass, making it the optimal solution for aggregation problems like this.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n) for grouping and counting, O(n log n) for sorting the result

⚡ Linearithmic

Space Complexity

O(k)

Space for k distinct artists in the result set

✓ Linear Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ id ≤ 10⁹
track_name and artist contain only printable ASCII characters
track_name and artist lengths are between 1 and 100 characters

Asked in

S Spotify 85 a Amazon 72 N Netflix 68 G Google 45 f Meta 38 🍎 Apple 31

This is a classic SQL aggregation problem solved optimally using GROUP BY with COUNT(*). The key insight is that SQL's GROUP BY clause efficiently groups records by artist in a single table scan, while COUNT(*) aggregates the group sizes. The ORDER BY clause handles the dual sorting criteria: occurrence count descending, then artist name ascending. Time complexity is O(n log n) due to sorting, with O(k) space for k unique artists.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Subquery for Each Artist)	O(n²)	O(n)	Use correlated subqueries to count occurrences for each distinct artist
SQL GROUP BY (Optimal)	O(n log n)	O(k)	Use SQL's GROUP BY clause with COUNT() to aggregate in a single pass

Brute Force (Subquery for Each Artist) — Algorithm Steps

Get all distinct artists from the table
For each artist, count their occurrences using a subquery
Order the results by count descending, then by artist name ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Find Distinct Artists

Scan table to find all unique artist names

Count Each Artist

For each artist, scan entire table again to count occurrences

Sort Results

Order by count descending, then artist name ascending

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_ARTISTS 1000
#define MAX_NAME_LEN 100

typedef struct {
    int id;
    char track_name[MAX_NAME_LEN];
    char artist[MAX_NAME_LEN];
} SpotifyRecord;

typedef struct {
    char artist[MAX_NAME_LEN];
    int occurrence_count;
} Result;

int compareResults(const void *a, const void *b) {
    Result *resA = (Result *)a;
    Result *resB = (Result *)b;
    if (resA->occurrence_count != resB->occurrence_count) {
        return resB->occurrence_count - resA->occurrence_count;
    }
    return strcmp(resA->artist, resB->artist);
}

int countArtistOccurrencesBruteForce(SpotifyRecord data[], int n, Result result[]) {
    char distinctArtists[MAX_ARTISTS][MAX_NAME_LEN];
    int distinctCount = 0;
    
    // Get distinct artists
    for (int i = 0; i < n; i++) {
        int found = 0;
        for (int j = 0; j < distinctCount; j++) {
            if (strcmp(data[i].artist, distinctArtists[j]) == 0) {
                found = 1;
                break;
            }
        }
        if (!found) {
            strcpy(distinctArtists[distinctCount], data[i].artist);
            distinctCount++;
        }
    }
    
    // Count occurrences for each artist
    for (int i = 0; i < distinctCount; i++) {
        int count = 0;
        for (int j = 0; j < n; j++) {
            if (strcmp(distinctArtists[i], data[j].artist) == 0) {
                count++;
            }
        }
        strcpy(result[i].artist, distinctArtists[i]);
        result[i].occurrence_count = count;
    }
    
    qsort(result, distinctCount, sizeof(Result), compareResults);
    return distinctCount;
}

int main() {
    // Parse input and call solution
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of the n distinct artists, we scan the entire table of n records

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing distinct artists and result set

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ id ≤ 10⁹
track_name and artist contain only printable ASCII characters
track_name and artist lengths are between 1 and 100 characters

67.2K Views

High Frequency

~8 min Avg. Time

2.1K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Subquery for Each Artist) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler