SQL Interview Question - Solution (Part - XIV) | Data Analyst

No video

SQL Interview Question - Solution (Part - XIV) | Data Analyst | Data Engineer

Рет қаралды 983

Күн бұрын

#sql #education #sqlfunctions #dataanalyst #dataengineers
#MeanLifeStudies #sqlinterview #datascience #interview #dataanalystinterview
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Q) Given the election and candidate's details. Find Who won the election.
Assuming the election is conducted in a city where everyone can vote for one or more candidates or choose not to vote.
Each person has 1 vote so if they vote for multiple candidates, their vote gets equally split across candidates.
Here are table creation and insertion statements:
------------------------------------------------------------------------
create table Election (
voter_id int,
candidate_id int
);
insert into Election values (1, 2),(2, 3),(2, 1),(2, 2),(3, null),(4, 3),(4, 2),
(4, 1),(5, 3),(6, 2),(6, 1),(7, 3),(7, 2),(7, 1),(8, null),(9, 2),(9, 1),(10, 2),(10,3);
create table candidates (id int, name varchar(10))
insert into candidates values(1,'Rah'),(2,'ketan'),(3,'mahi')

Пікірлер: 9

@dasubabuch1596 Ай бұрын

Hi Sir, This is my query. with t as ( select e.voter_id,c.id,c.name from election e inner join candidates c on e.candidate_id = c.id where e.candidate_id is not null ),t1 as ( select count(voter_id)over(partition by id) as cnt, voter_id, name, id from t ), t2 as ( select id,name, sum(cnt) as total from t1 group by id,name ), t3 as ( select name, dense_Rank()over(order by total desc) as rnk from t2 ) select name from t3 where rnk = 1;

@namangarg7023 Күн бұрын

This one is tough

@AbhijitPaldeveloper Ай бұрын

Hi, This is my 2 solutions. 2nd one is similar to your solution. SELECT name FROM(SELECT name, total, DENSE_RANK() OVER(ORDER BY total DESC) as rnk FROM(SELECT e.candidate_id, c.name, COUNT(e.voter_id) as total FROM `election` e join candidates c on e.candidate_id = c.id GROUP BY e.candidate_id) as x) as y WHERE rnk=1; SELECT name FROM(SELECT candidate_id, name, DENSE_RANK() OVER(ORDER BY SUM(voter_value) DESC) as rnk FROM(SELECT e.*, c.name, 1.0/COUNT(e.candidate_id) OVER(PARTITION BY e.voter_id) as voter_value FROM `election` e join candidates c on e.candidate_id = c.id WHERE e.candidate_id IS NOT NULL) as x GROUP BY candidate_id) as y WHERE rnk = 1

@nalluriranapratap4926 Ай бұрын

Hi ,i have one doubt that one voter usually caste their vote for one candidate as it comes under one to many(or vice versa) relation. But in our scenario it has many to many relations which practically won't possible right ?

@MeanLifeStudies Ай бұрын

Kindly go through comments under this problem

@manojroyal7180 Ай бұрын

is it correct bro with cte as( select c.*,count(voter_id) as voters_count, candidate_id from candidates c join election e on c.id=e.candidate_id group by e.candidate_id, c.id,c.name) select name from cte where voters_count=(select max(voters_count) from cte)

@MeanLifeStudies Ай бұрын

Hi, Yes, it is correct. But it is possible only when voters voted for multiple candidates, then their value of vote is 1 for each. But our condition is not similar to the actual of our election. If a voter is votes for three candidates, the value of each is shared by three parts. so we should consider that also right?

@manojroyal7180 Ай бұрын

@@MeanLifeStudies yes I missed it thanks for reply