LeetCode Hard SQL Problem | Employee Median Salary Company Wise

In this way we will discuss a LeetCode hard problem to find median salary of employees for each company.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table employee
(
emp_id int,
company varchar(10),
salary int
);

insert into employee values (1,'A',2341)
insert into employee values (2,'A',341)
insert into employee values (3,'A',15)
insert into employee values (4,'A',15314)
insert into employee values (5,'A',451)
insert into employee values (6,'A',513)
insert into employee values (7,'B',15)
insert into employee values (8,'B',13)
insert into employee values (9,'B',1154)
insert into employee values (10,'B',1345)
insert into employee values (11,'B',1221)
insert into employee values (12,'B',234)
insert into employee values (13,'C',2345)
insert into employee values (14,'C',2645)
insert into employee values (15,'C',2645)
insert into employee values (16,'C',2652)
insert into employee values (17,'C',65);

#sql #dataengineer

[aatifhussain]

Hi Ankit. Thank you for the solution. My approach to this will be:


with cte as
(
select *,
ROW_NUMBER() over(partition by company order by salary) as rn
, count(1) over(partition by company) as cn
from employee)
select company,
avg(salary) as med
from cte
where rn in (floor((cn+1)*1.0/2), ceiling((cn+1)*1.0/2))
group by company

[shekharagarwal]

Thanks Ankit and really liked your where clause trick . Initially I tried too much mathematical way :

with cte1 as (
Select *, row_number() over ( partition by company order by salary ) as rn
, 1.0 * count(salary) over ( partition by company )/2 as cnt from employee )
Select company, avg(salary) as median_sal from cte1
where (
( rn = cnt ) or (rn -1 =cnt) -- even number of rows
or (rn - 0.5 = cnt ) -- odd number of rows
)
group by company

[mantisbrains]

select avg(salary), company from
(select * from
(select company, salary, count(company) over (partition by company) as total_count, row_number() over (partition by company order by salary) rn from employee)d
where rn between total_count*1.0/2 and total_count*1.0/2+1 )f
group by company;


Thank you, Ankit!

[KisaanTuber]

Thanks Ankit for sharing this approach. Definitely helpful for calculating median.

[VivekYadav-lnpq]

this is my way of solving
select distinct company,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER by salary) over (PARTITION by company) as medium_Sal

FROM empl

[AyushGupta-xdth]

Hi Ankit, Good solution, below is my solution :

with temp as (

(select a.company, case when max(a.r)%2=1 then max(a.r)/2+1

else max(a.r)/2 end as value


from (select id, company, salary, row_number() over (partition by company order by salary) as r
from employee) a
group by a.company)
union
(select a.company, case when max(a.r)%2=0 then max(a.r)/2+1 end as value
from (select id, company, salary, row_number() over (partition by company order by salary) as r
from employee) a
group by a.company)
)
select p.id, p.company, p.salary
from
(select id, company, salary, row_number() over (partition by company order by salary) as r
from employee) p
inner join temp
on p.r=temp.value and p.company=temp.company;

[kunaljain-ll]

Thank you sir !! this is a genius approach !

[kanchankumar]

This is great.. Sql baba - make video on optimization techniques

[sabinakhatun]

my approach will be

with cte as ( select *, row_number() over(partition by company order by salary desc) as rank ,
row_number() over(partition by company order by salary asc) as rank1
from employee )

select distinct company,
sum(salary) over(partition by company)/2 from cte where rank-rank1= 1 or rank-rank1= -1

[AJOnline_]

Another tricky way of doing this one:

with cte as (
select *
, row_number() over (partition by company order by salary asc) -
cast( count(1) over (partition by company) as float )/2 as mid_level
from employee ),
cte2 as (
select Company, Salary from cte
where mid_level = 0 or mid_level = 0.5 or mid_level = 1 )
select Company,
avg(salary) as median
from cte2
group by Company

[abb_raj]

my approach :
with median as (
select *
, row_number() over(partition by company order by salary asc) rn
, count(*) over(partition by company) cnt
from employee)
, median_2 as (
select *
, case when cnt%2 = 1 then ceiling((cnt+1)/2) end one1
, case when cnt%2 = 0 then ceiling(cnt/2) end zero1
, case when cnt%2 = 0 then ceiling((cnt+1)/2) end zero2
from median)
select company, avg(salary)
from median_2 where rn = one1 or rn = zero1 or rn = zero2
group by company

[anirvansen]

Using two row number function (As discussed by ankit)
MYSQL

with base as (select *,
abs(cast(row_number() over(partition by company order by salary asc) as signed) -
cast(row_number() over(partition by company order by salary desc) as signed)) as abs_diff
from employee order by company, salary)
select company, round(avg(salary)) as median_salary from base where abs_diff <=1 group by company

[TechWithViresh]

select distinct company, avg(salary) over (partition by company) as 'Median' from(
select *, row_number() over (partition by company order by salary asc) as r_asc, row_number() over (partition by company order by salary desc) as r_desc from employee) t
where r_asc in (r_desc, r_desc-1, r_desc+1)

[andreanlobo]

i got the answer using the solution you has shown in a previous video of yours but i believe this solution would work much better. Thanks

[muddassirnazar]

Alternate way of doing this
select company,
sum(case when cnt%2=0 and (rn=cnt/2 or rn=(cnt/2)+1) then salary
when cnt%2!=0 and rn=cnt/2 then salary
else 0 end)/2 as median
from
(select *, row_number() over(partition by company order by salary) as rn,
count(*) over(partition by company) as cnt
from employee) qry
group by company

[kavyatripathi]

loved the approach. Easy and understandable

[shankrukulkarni]

select * from employee ;

select company, avg(salary) from
(select
*,
row_number() over(partition by company order by salary asc) as rn1,
count(*) over(partition by company ) as total_count
from employee)a
where rn1 between total_count/2 and total_count/2+1
group by company

[skkholiya]

with salary_rn as(
select *,
count(company) over(partition by company) cnt,
row_number() over(partition by company order by salary) rn
from employee
)

select company, avg(case
when cnt%2=0 and rn = cnt/2 or rn = (cnt/2 + 1) then salary
when cnt%2!=0 and ceil(cnt/rn) +1 = rn then salary
end) median
from salary_rn
group by company

[vikash]

This question was asked for zepto senior product analyst role

[Dhanushts-gx]

with cte1 as
(select *, row_number() over(partition by company order by salary) rn,
row_number() over(partition by company order by salary desc) rn2
from employee)
select company, round(avg(salary)) salary from cte1 where abs(rn2-rn)<=1
group by company
(easy solution)