Artist Presence Frequency on Streaming Chart

Easy

A music analytics platform stores ranking-list entries in a table and needs a frequency summary by artist. Table: Spotify id: unique row identifier track_name: song title recorded for that ranking row artist: artist name for that ranking row Occurrence rule: Each row contributes exactly one occurrence to its artist. Task: Return one row per artist with: artist occurrences Sorting rules: Sort by occurrences in descending order. If two artists have the same occurrences, sort by artist in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Spotify:
| id      | track_name         | artist     |
|---------|--------------------|------------|
| 303651  | heart wont forget  | sia        |
| 1046089 | shape of you       | ed sheeran |
| 33445   | im the one         | dj khalid  |
| 811266  | young dumb broke   | dj khalid  |
| 505727  | happier            | ed sheeran |

Output

[
  {"artist":"dj khalid","occurrences":2},
  {"artist":"ed sheeran","occurrences":2},
  {"artist":"sia","occurrences":1}
]

Explanation

dj khalid and ed sheeran each appear twice, so the tie is resolved by artist name ascending. sia appears once.

Example 2

Input

Spotify:
| id | track_name     | artist   |
|----|----------------|----------|
| 1  | morning lights | aurora   |
| 2  | sky pulse      | aurora   |
| 3  | afterburn      | blaze    |
| 4  | runway         | comet    |
| 5  | static noise   | comet    |
| 6  | deep horizon   | comet    |

Output

[
  {"artist":"comet","occurrences":3},
  {"artist":"aurora","occurrences":2},
  {"artist":"blaze","occurrences":1}
]

Explanation

comet has the highest count, followed by aurora, then blaze.

Example 3

Input

Spotify:
[]

Output

[]

Explanation

No rows means no artists to report.

Constraints

0 <= number of rows in Spotify <= 900
id values are unique
1 <= id <= 1000000000
1 <= length(track_name) <= 80
1 <= length(artist) <= 80
track_name and artist use printable ASCII text
Each row contributes one occurrence to exactly one artist
Output columns must be artist and occurrences
Sort by occurrences DESC, then artist ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns artist and occurrences

Spotify: | id | track_name | artist | |---------|--------------------|------------| | 303651 | heart wont forget | sia | | 1046089 | shape of you | ed sheeran | | 33445 | im the one | dj khalid | | 811266 | young dumb broke | dj khalid | | 505727 | happier | ed sheeran |

Spotify: | id | track_name | artist | |----|----------------|----------| | 1 | morning lights | aurora | | 2 | sky pulse | aurora | | 3 | afterburn | blaze | | 4 | runway | comet | | 5 | static noise | comet | | 6 | deep horizon | comet |