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

Подписаться 940

50% 1

#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/mahendra204
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')

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 8

@dasubabuch1596 27 дней назад

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;

@AbhijitPaldeveloper 26 дней назад

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 17 дней назад

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 17 дней назад

Kindly go through comments under this problem

@manojroyal7180 20 дней назад

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 20 дней назад

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 20 дней назад

@@MeanLifeStudies yes I missed it thanks for reply