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

[@arupchandra8602]

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;

[@zaravind4293]

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;

[@Code-Con]

Yeah all right with this one 👍🏾