Find songs that have ranked in the top position. Output the track name and the number of times it ranked at the top. Sort your records by the number of times the song was in the top position in descending order.
table name:spotify_worldwide_daily_song_ranking
CREATE TABLE [dbo].[spotify_worldwide_daily_song_ranking](
[id] [int] NOT NULL,
[position] [int] NULL,
[trackname] [varchar](250) NULL,
[artist] [varchar](50) NULL,
[streams] [int] NULL,
[url] [varchar](250) NULL,
[date] [datetime] NULL,
[region] [varchar](50) NULL,
CONSTRAINT [PK_spotify_worldwide_daily_song_ranking] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Solution:
select count(position) as toprank,trackname from spotify_worldwide_daily_song_ranking where position=1
group by trackname
order by toprank desc
Output:
