569. Median Employee Salary
Question:
Solution:
select
e.id,
e.company,
e.salary
from(
select
id,
company,
salary,
row_number() over(partition by company order by salary) as r
from employee
) e
join (
select
company,
floor((count(*)+1)/2) as median_row_lower,
floor((count(*)+2)/2) as median_row_upper
from employee
group by 1
) m
on e.company = m.company
and (
e.r >= m.median_row_lower
and e.r <= median_row_upper
)
order by 2,3
Note: When the company has odd number of employees, the median salary will be person whose salary is in the middle among all employees of the same company. However, when the company has even number of employees, the query will need to return employees with two middlemost salaries.
570. Managers with at Least 5 Direct Reports
Question:
Solution:
select
e.Name
from (
select distinct
Id,
Name
from Employee
) e
join (
select
ManagerId,
count(distinct Id) as direct_reports
from Employee
where ManagerId is not null
group by 1
having count(distinct Id) >= 5
) m
on e.Id = m.ManagerId
571. Find Median Given Frequency of Numbers
Question:
Solution:
select
avg(Number) as median
from (
select
Number,
Frequency,
cum_freq,
cum_freq_desc,
middle,
case
when cum_freq >= middle and cum_freq_desc >= middle then 1
else 0
end as flag
from(
select
Number,
Frequency,
sum(Frequency) over(order by Number) as cum_freq,
sum(Frequency) over(order by Number desc) as cum_freq_desc,
round(t.total/2,0) as middle
from Numbers n
left join (
select
sum(Frequency) as total
from Numbers
) t on 1=1
) a
) b
where b.flag = 1
order by 1
574. Winning Candidate
Question:
Solution:
select
a.Name
from(
select
c.Name,
count(distinct v.id) as votes
from Candidate c
left join Vote v
on c.id = v.CandidateId
group by 1
) a
order by a.votes desc
limit 1