Back to questions

SQL Tutorial Lesson: Album Releases SQL Interview Question

SQL Tutorial Lesson: Album Releases

SQL Interview Question

As the lead data analyst for a prominent music event management company, you have been entrusted with a dataset containing concert revenue and detailed information about various artists.

Write a query that categorizes the artists based on their album release count. Display the output of the artist name, and album categort, sorted in ascending order of the artist's name.

The album category will be based on the number of albums released by the artists or bands:

If an artist has released 7 or more albums, they fall under the "Platinum" category.
If an artist has released between 3 and 6 albums (inclusive), they fall under the "Gold" category.
If an artist has released 2 or fewer albums, they fall under the "Silver" category.

Schema:

Column Name	Type	Description
artist_id	integer	A unique identifier for each artist or band performing in the concert.
artist_name	varchar(100)	The name of the artist or band performing in the concert.
genre	varchar(50)	The music genre associated with the concert.
concert_revenue	integer	The total revenue generated from the concert.
year_of_formation	integer	The year that the artist or band was formed.
country	varchar(50)	The country of origin or residence of the artist or band.
number_of_members	integer	The number of members in the band.
album_released	integer	The total number of albums released by the artist or band.
label	varchar(100)	The record label or music company associated with the artist or band.

Example Input:

artist_id	artist_name	genre	concert_revenue	year_of_formation	country	number_of_members	album_released	label
103	Taylor Swift	Pop	700000	2004	United States	1	9	Republic Records
104	BTS	K-Pop	800000	2013	South Korea	7	7	Big Hit Music
105	Adele	Pop	600000	2006	United Kingdom	1	3	Columbia Records
109	Blackpink	K-Pop	450000	2016	South Korea	4	5	YG Entertainment
110	Maroon 5	Pop	550000	1994	United States	5	7	Interscope Records

Solution

Step 1: Define album categories

Let's define the album categories based on the number of albums released by the artists or bands:

"Platinum" category: Artists with 7 or more albums.
"Gold" category: Artists with 3 to 6 albums (inclusive).
"Silver" category: Artists with 2 or fewer albums.

Step 2: Write the query with CASE statement

To categorize the artists based on their album release count, we'll use the statement in our query.

If is greater than or equal to 7, we assign the value 'Platinum' to the column.
If is between 3 and 6 (inclusive), we assign the value 'Gold' to the column.
For all other cases (when is 2 or less), we assign the value 'Silver' to the column.

That's it! You've successfully categorized the artists based on their album release count using the statement in SQL.

Difficulty

Medium

Input

Output