LeetCode Hard SQL Problem | Employee Median Salary Company Wise
ฝัง
- เผยแพร่เมื่อ 8 ก.ย. 2024
- 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:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
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
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
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
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
Thanks Ankit for sharing this approach. Definitely helpful for calculating median.
This is great.. Sql baba - make video on optimization techniques
Sure bro
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)
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)
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;
Thanks for posting 👏
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
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
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
with cte_2 as (
select *, case when rn_asc - 1 = rn_desc or rn_desc - 1 = rn_asc or rn_asc = rn_desc then salary end as salary_new
from (select company, salary,
row_number() over(partition by company order by salary) as rn_asc,
row_number() over(partition by company order by salary desc) as rn_desc
from employee) a
order by company, salary)
select company, round(avg(salary_new),0) as median_salary
from cte_2
where salary_new is not null
group by company
This question was asked for zepto senior product analyst role
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
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
Thanks Ankit bhai 🙏🏻
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
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
Excellent
can we use mean function instead? with a as (
select * from employee
order by company,salary
)
select company,median(salary) from a group by company
Here is another easy solution
with cte as(
select *,COUNT(emp_id)over (partition by company) cnt,ROW_NUMBER() over(partition by company order by salary) rnk
from #employee)
select company,AVG(salary) from cte where rnk in ((cnt/2)+1,CAST(cnt as float)/CAST(2 as float)) group by company
with cte1 as(
select emp_id,company,salary,row_number() over(partition by company order by salary ) rn,
count(1) over(partition by company) total_cnt
from employee
),cte2 as(
select company,salary,rn,total_cnt*1.0/2 as c1,total_cnt*1.0/2 + 1 as c2 from cte1
where rn BETWEEN total_cnt*1.0/2 and total_cnt*1.0/2 + 1
)
select company,avg(salary) median from cte2 group by company
loved the approach. Easy and understandable
Great to hear!
with cte as (select *, row_number() over(partition by company order by salary)as arnk,
row_number() over(partition by company order by salary desc)as drnk
from employee)
select distinct company, avg(salary) from cte
WHERE arnk = drnk or arnk= drnk+1 or arnk=drnk-1
group by company
can you explain that where condition brother.
select company, avg(salary) as median_sal
from (
select *,
row_number() over(partition by company order by company,salary)rn, COUNT(*) OVER (partition by company) AS total_rows
from employee) a
where rn IN (FLOOR((total_rows + 1) / 2.0), CEIL((total_rows + 1) / 2.0))
group by company
SQL Server Solution:
with cte as (
SELECT *, count(1) over (partition by company) AS employee_counts,
ROW_NUMBER() OVER (PARTITION by company ORDER BY (salary)) AS rn
FROM employee
)
SELECT company, AVG(salary)FROM cte
where rn between (employee_counts * 1.0)/2 and (employee_counts*1.0)/2 + 1
GROUP BY company
great explanation...........
SELECT DISTINCT(company), ROUND(avg(salary)) from
(SELECT * , ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary ASC) as asc_sal,
ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary DESC) as desc_sal
FROM employee ) as A
WHERE asc_sal in (desc_sal, desc_sal+1, desc_sal-1)
GROUP BY company ;
my approach is mentioned below , however Ankit's approach is better:
with evenflag_cte as (
select company , case when cnt%2==0 then 1 else 0 end as evenflag from (
select company , count(1) cnt from employee group by company) t
),
emp_cte as(
select e.*, row_number() over (partition by e.company order by salary ) as rn, (count() over (partition by e.company))/2 as emp_count, c.evenflag from employee e join evenflag_cte c on e.company=c.company)
select company , avg(salary) from emp_cte where rn=emp_count and evenflag=1 or rn=emp_count+1 and evenflag=1 or rn=emp_count and evenflag=0 group by company
very well explained
Glad you think so!
Very useful content, please make a video on how to optimize SQL queries (techniques and good practices)
Thank you. Sure.
My Solution :-
with cte as
(select * from
(select *, ROW_NUMBER() over (partition by company order by salary asc)rn_asc
, row_number() over (partition by company order by salary desc)rn_desc from median_companywise)t
where rn_asc - 1 = rn_desc or rn_asc +1 =rn_desc)
select company, AVG(salary)as median_salary from cte
group by company
Brilliant
When @ankitbansal6 posts a video, I like it first and then watch it because I know it will be epic.
Legend!
Very helpful brother
Glad to hear that
long query
with cte_grp as (
select company , floor((count(1)/2 )+1 )sec ,ceil(count(1)/2) med
from employee group by company ) ,
cte_sal as(
select * , count(1) over(partition by e.company) ,row_number() over (partition by e.company order by company,salary) id
from employee e
order by company ,salary
)
select company ,avg(salary ) from (
select e.company company ,salary
from cte_sal e
where (e.company,e.id) in ( select company,sec from cte_grp)
union all
select e.company ,salary
from cte_sal e
where (e.company,e.id) in (select company , med from cte_grp )
) a group by company
can some one answer this SQL query that produces the median
rnk
50,60,45,23,89,93,67
output should be 60
please do reply asap
please make a playlist
Can you please put the DDL and insert queries? You usually put that and it is super helpful
Just added in description.
with even as (
select *,row_number() over(partition by company order by salary) as rn1,
count(1) over(partition by company) as ct1
from employee
where company in (select company as c from employee group by company
having count(company)%2 = 0)
),
odd as
(select *,row_number() over(partition by company order by salary) as rn2,
count(1) over(partition by company) as ct2
from employee
where company in
(select company as c from employee group by company
having count(company)%2 != 0)
)
select company,avg(salary) as median from even
where rn1 between ct1/2 and ct1/2 + 1
group by company
union all
select company,avg(salary) as median from odd
where rn2 between ct2/2 and ct2/2 + 1
group by company
Good one 🙂
Thank you! Cheers!
❤🎉🎉🎉 you
WITH cte AS (
SELECT
*,
RANK() OVER(PARTITION BY company ORDER BY salary ASC) as rn_asc,
RANK() OVER(PARTITION BY company ORDER BY salary DESC) as rn_dsc
FROM
(
SELECT
*
FROM
employee_sep
ORDER BY
salary ASC
) AS a
)
SELECT
company,
SUM(CASE
WHEN
ABS(CAST(rn_asc AS signed) - CAST(rn_dsc AS signed)) = 1 THEN salary ELSE 0 END) /2 AS median_A
FROM
cte
GROUP BY
1
ORDER BY
company
;
If anyone wants to display Emp_id also then add this in first line
STRING_AGG(emp_id, ', ') AS EmployeeIDs
I tried doing like below.
with employee_with_rownum as (
select
*,
row_number() over(partition by company order by salary) as row_num
from
employee
),
-- Companies having even number of employees
salary_med_with_even as (select
er.company,
AVG(er.salary * 1.0) as salary
from
employee_with_rownum er
inner join
(
select
company,
count(*)/2 + 1 as median_posn
from
employee e
group by
company
having
count(*) % 2 = 0
) m
on
(er.row_num = m.median_posn OR er.row_num = m.median_posn - 1) AND
er.company = m.company
group by
er.company),
salary_med_with_odd as (
-- Companies having odd number of employees
select
er.company,
er.salary
from
employee_with_rownum er
inner join
(
select
company,
count(*)/2 + 1 as median_posn
from
employee e
group by
company
having
count(*) % 2 0
) m
on
er.row_num = m.median_posn AND
er.company = m.company)
select * from salary_med_with_even
union all
select * from salary_med_with_odd
Good attempt. Just that the query is big 🙂
with cte as (
select *,
row_number() over(partition by company order by salary) as rank_
from employee),
cte2 as(
select company, max(rank_) as max_rank
from cte
group by 1),
cte3 as(
select company,
round(case when mod(max_rank,2) = 0 then (max_rank/2) +1 else ((max_rank-1)/2)+1 end ,0)as max_r,
round(case when mod(max_rank,2) = 0 then (max_rank/2) else ((max_rank-1)/2)+1 end,0) as min_r
from cte2),
cte4 as(
select a.*, b.salary as s_ from cte3 a inner join cte b
on a.max_r = b.rank_ and a.company = b.company),
cte5 as(
select a.*, b.salary as s__ from cte4 a inner join cte b
on a.min_r = b.rank_ and a.company = b.company)
select company, (s_+s__)/2 as median_sal
from cte5;