Spotify SQL Interview Question | Advanced SQL

[@hairavyadav6579]

if someone inserting value may you get error for the column rank because it is function , so if we want to use rank as a column name use this
`rank` then you can able to insert value in rank column ... ` this symbol is above the tab key in keyboard

[@RishabhSharma-wj4qv]

Thanks dude

[@002anbarasisanthosh9]

select a.artist_name
, dense_rank() over( order by count(g.song_id) desc) as Rank_of_artist from artists a
cross apply (select * from songs where artist_id =a.artist_id) s
cross apply ( select * from global_song_rank where song_id =s.song_id and rank

[@prakxsh_]

my approach is
with cte as (select count(*) as art_count,a.artist_name from artists a join songs s on s.artist_id = a.artist_id
join global_song_rank g on g.song_id = s.song_id
group by a.artist_id)
select dense_rank() over ( order by art_count desc) as rn ,artist_name from cte;

[@HARSHRAJ-gp6ve]

with cte as(
select artist_name,song_id FROM artists JOIN songs ON artists.artist_id=songs.artist_id
),cte1 as(
select cte.*,rank1 from cte JOIN global_song_rank ON cte.song_id=global_song_rank.song_id
),cte2 as(
select artist_name,COUNT(*) as x1 FROM cte1 where rank1

[@hairavyadav6579]

My approach
with cte as (select a.artist_name as name,s.song_id from artists as a join songs as s on a.artist_id = s.artist_id),
cte2 as(select c.name,count(*) as number_of_time from cte as c join global_song_rank as gr on c.song_id = gr.song_id where gr.rank

[@VikasChavan-v1c]

select a.artist_name,count(*),dense_rank() over (order by count(*) desc) as ranking from global_song_rank r join songs s on r.song_id=s.song_id join artists a on a.artist_id=s.artist_id
where r.rnk

[@pratyushkumar8567]

with cte as(
select artist_name ,g.song_id,g.rank,count(g.song_id) over(partition by artist_name) as cnt_
from artists as a join songs as s on a.artist_id=s.artist_id
join global_song_rank as g on s.song_id=g.song_id
where rank

[@prajju8114]

with cte_songs as
(
select s.song_id,a.artist_id,a.artist_name,a.label_owner,s.name,g.day,g.rank from songs as s join artists as a on s.artist_id=a.artist_id join global_song_rank as g on s.song_id=g.song_id
),
cte_2 as
(
select artist_name, count(song_id) as artist_count from cte_songs group by artist_name
)
select artist_name,dense_rank() over(order by artist_count desc) as 'rank' from cte_2

[@Naliyadav]

With CTE AS
(
select A.Artist_Id,A.Artist_name ,
CounT(rank) As cnt
from Artists A
join Songs S
on S.artist_id=A.Artist_id
join global_song_rank G
on G.Song_Id=S.Song_Id
where g.rank

[@Savenature635]

with cte as (select distinct a.artist_name,s.artist_id,count(g.song_id) as number_of_appearances
from artists a
join songs s
on a.artist_id=s.artist_id
join global_song_rank g
on s.song_id=g.song_id
where g.rankk

[@himanshubasra4451]

select artist_name, dense_rank() over(order by sum(total_appearnce) desc) as artist_rank
from
(
select s.*, a.artist_name, count(g.song_id) as total_appearnce
from songs as s
join artists as a on s.artist_id = a.artist_id
left join global_song_rank as g on s.song_id = g.song_id
group by s.song_id, a.artist_name
)
group by 1

[@chaitanyagupta9767]

SELECT Top 5 A.artist_name, DENSE_RANK() OVER (order by count(*) desc) AS artist_rank FROM (SELECT a.*,s.name, gr.*FROM (SELECT * FROM artists) AS A left join (Select * from songs) as S
ON A.artist_id = S.artist_id LEFT JOIN (SELECT * FROM global_song_rank) AS gr ON
S.song_id=gr.song_id WHERE gr.song_id IS NOT NULL and gr.rank

[@saikatmazumder206]

with cte as (select a.artist_id,a.artist_name,s.song_id,s.name,g.day,g.rnk from artists as a
join songs as s on a.artist_id=s.artist_id join global_song_rank as g on s.song_id=g.song_id)
select artist_name,dense_rank()over(order by count(rnk)desc) as artist_rank
from cte group by 1 having count(rnk)

[@prajju8114]

query is incorrect, with cte as
(
select a.artist_id,a.artist_name,s.song_id,s.name,g.day,g.rank as rnk from artists as a
join songs as s on a.artist_id=s.artist_id join global_song_rank as g on s.song_id=g.song_id
)
select artist_name,dense_rank()over(order by count(rnk) desc) as artist_rank
from cte group by artist_name having count(rnk)

[@rohit_vora]

with cte as
(select artist_name, count(1) cnt
from
(select artist_name, s.song_id , a.artist_id, rank from artists a
join songs s on s.artist_id = a.artist_id
join global_song_rank gr on gr.song_id = s.song_id
where rank