PWC Interview Question for Data Analyst Role | SQL Intermediate Question 14 

with cte as( select candidate_id,datepart(year,vote_date)*100+''+datepart(month,vote_date) as weeks ,count(voter_id) counts from elections group by datepart(year,vote_date)*100+''+datepart(month,vote_date),candidate_id ),cte2 as( select * ,RANK() OVER(PARTITION BY weeks order by counts desc)rn from cte) select * from cte2 where rn=1;

HI bro, We can use dense rank in the first select statement itself so that we can reduce one cte. Please find my solution below select month,cid,votes from ( select extract(month from vdate) month, cid, count(vid) votes, dense_Rank() over(partition by extract(month from vdate) order by count(vid) desc) rk from pwc group by extract(month from vdate),cid order by 1 ) where rk=1;