Assume there are three Spotify tables containing
information about the artists, songs, and music charts. Write a query to
determine the top 5 artists whose songs appear in the Top 10 of the global_song_rank
table the highest number of times. From now on, we'll refer to this ranking
number as "song appearances".
Output the top 5 artist names in ascending order
along with their song appearances ranking (not the number of song appearances,
but the rank of who has the most appearances). The order of the rank should
take precedence.
For example, Ed Sheeran's songs appeared 5 times
in Top 10 list of the global song rank table; this is the highest number of
appearances, so he is ranked 1st. Bad Bunny's songs appeared in the list 4, so
he comes in at a close 2nd.
Assumptions:
· If two artists' songs have
the same number of appearances, the artists should have the same rank.
· The rank number should be
continuous (1, 2, 2, 3, 4, 5) and not skipped (1, 2, 2, 4, 5).
table: artist
table: songs
table: global_song_rank
Solution:
with cte as
(
select a.artist_name,dense_rank() over (order by count(*) desc) as artist_rank from global_song_rank as g
join songs as s on g.song_id = s.song_id
join artists as a on s.artist_id=a.artist_id
where g.rank between 1 and 10
group by artist_name
)
select * from cte where artist_rank between 1 and 5
Output:
SQL Script:
GO
CREATE TABLE [dbo].[artists](
[artist_id]
[int] NULL,
[artist_name]
[nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[global_song_rank](
[day]
[int] NULL,
[song_id]
[int] NULL,
[rank]
[int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[songs](
[song_id]
[int] NULL,
[artist_id]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[artists] ([artist_id], [artist_name]) VALUES (101, N'Ed Sheeran')
GO
INSERT [dbo].[artists] ([artist_id], [artist_name]) VALUES (120, N'Drake')
GO
INSERT [dbo].[global_song_rank] ([day], [song_id], [rank]) VALUES (1, 45202, 5)
GO
INSERT [dbo].[global_song_rank] ([day], [song_id], [rank]) VALUES (3, 45202, 2)
GO
INSERT [dbo].[global_song_rank] ([day], [song_id], [rank]) VALUES (1, 19960, 3)
GO
INSERT [dbo].[global_song_rank] ([day], [song_id], [rank]) VALUES (9, 19960, 15)
GO
INSERT [dbo].[songs] ([song_id], [artist_id]) VALUES (45202, 101)
GO
INSERT [dbo].[songs] ([song_id], [artist_id]) VALUES (19960, 120)
GO