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

Hey all, this question was asked in a PWC interview and u may encounter similar question in any data related role.
Question - For each month find the candidate with highest number of votes.
DDL Commands :-
create table elections(
candidate_id int,
voter_id int,
vote_date date)
insert into elections
values(1,01,'2023-12-01'),
(1,02,'2023-12-05'),
(1,03,'2023-12-02'),
(2,01,'2023-12-01'),
(1,01,'2023-11-02'),
(2,03,'2023-11-02'),
(2,02,'2023-11-05')
select * from elections;
#amazon #facebook #meta #flipkart #google #sqldeveloper #dataanalytics #dataengineering #sqlserver #interviewquestions #placement #college #

[@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 👍🏾