Microsoft SQL Interview Question for Data Engineer Position | Data Analytics

In this video we will discuss a problem asked in a Microsoft interview for Data Engineer Position. In this problem we have to write a SQL to build a team with a combination of seniors and juniors within a given salary budget.
We will discuss the step by step approach to solve the problem and see how powerful are SQL common table Expressions (CTE).
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 candidates (
emp_id int,
experience varchar(20),
salary int
);
delete from candidates;
insert into candidates values
(1,'Junior',10000),(2,'Junior',15000),(3,'Junior',40000),(4,'Senior',16000),(5,'Senior',20000),(6,'Senior',50000);

#sql #dataengineer #microsoft #interview

[rashmidutta]

just wow!! I thought that this can be done by recursion!! But you did it so gracefully

[pinaakgoel]

Hi Ankit, I did via following approach :-

with senior_cte as (select *, sum(salary) over(order by salary) as rn_sum_sen
from candidates where experience = 'Senior'),
junior_cte as (select *, sum(salary) over(order by salary) as rn_sum_jun
from candidates where experience = 'Junior')

select emp_id, experience from senior_cte where rn_sum_sen<70000
union all
select emp_id, experience from junior_cte where rn_sum_jun<(select 70000-sum(salary) from senior_cte where rn_sum_sen<70000)

[Useracwqbrazy]

The solution i did in first attempt...am regularly watching ur playlist from 15 days....thank u Ankit...

with cte as
(select *, sum(salary) over(partition by experience order by experience, salary)
as sum_sal from candidates
)
,
cte1 as(select * from cte where sum_sal<='70000' and experience='Senior'
union all
select * from cte where experience='Junior'
)

select emp_id, experience, salary from (
select *, sum(salary) over(order by experience desc, salary)
as summation_sal from cte1
)t
where summation_sal<='70000'

[shekharagarwal]

Thanks a lot ank(IT), you always have different approach which becomes adding to our knowledge bank. I have used your earlier dynamic union approach of Sachin milestone problem .

with cte as
( SELECT *
, SUM(salary) over ( partition by experience order by salary) as runing_sum
FROM candidates
)
, team_budget as
( SELECT 70000 as budget )
, cte2 as
( SELECT *
, SUM(c.salary) over ( order by c.experience Desc, c.salary) as runing_sum1
FROM cte c JOIN team_budget tb
ON c.runing_sum <=tb.budget
)

SELECT * FROM cte2 WHERE runing_sum1<=budget

[sourabhgupta]

Amazing question Ankit. Here is my solution to that:
with cte as(
select *, sum(salary) over(partition by experience order by experience desc, salary asc)
as running_sal from candidates
),
senior_hired as
(select * from cte where experience='senior' and running_sal<=70000)
, juinior_hired as(
select * from cte where experience='Junior' and
running_sal<=(70000-(select max(running_sal) from senior_hired))
)
select emp_id, experience, salary from senior_hired union all
select emp_id, experience, salary from juinior_hired order by emp_id

[mantisbrains]

Thanks, Ankit! It never occurred to me that we could use running sum for this.

[thebongwanderluster]

Your approach is simpler and cleaner.My own complicated approach:
with cte as(
select emp_id, experience, salary, SUM(salary) over (partition by experience order by experience desc, salary asc rows between unbounded preceding and current row)-salary AS CUM_SUM FROM CANDIDATES order by experience desc),
senior_cte as(
SELECT emp_id, experience, salary, CUM_SUM, (70000-CUM_SUM) as remaining, case when (70000-CUM_SUM)>=salary then 'select' else 'reject' end as status FROM CTE where experience like 'Senior'),
junior_cte as(
SELECT emp_id, experience, salary, CUM_SUM, case when experience='Junior' then (select min(remaining) from senior_cte) END -CUM_SUM as remaining, case when (select min(remaining) from senior_cte)-CUM_SUM >=salary then 'select' else 'reject' end as status FROM cte where experience like 'Junior'
)
select emp_id, experience, salary from senior_cte where status='select'
UNION
select emp_id, experience, salary from junior_cte where status='select'
order by emp_id
;

[anshumansrivastava]

Hello Ankit,
This is my approach.

with cte1 as (

select *, (sum(salary) over(order by experience desc, salary asc)) as running_sal
from candidates
)
, cte2 as(
select * from cte1
where running_sal<70000
union
select * from cte1
where experience='Junior'),
cte3 as(
select *, (sum(salary) over(order by experience desc, salary asc)) as new_running_sal from cte2
order by experience desc, salary asc)

select * from cte3
where new_running_sal <70000

[Satish_____Sharma]

Question is just amazing

here is my try using MYSQL👍

with cte as (SELECT *, 70000-sum(salary) over (partition by experience order by emp_id) as total
FROM candidates where experience='Senior')
, cte1 as (select *, 70000-sum(salary) over () as reamaining from cte where total>=0)
, cte2 as (SELECT *, (select max(reamaining) from cte1)-sum(salary) over (partition by experience order by emp_id) as total
FROM candidates where experience='Junior')

select emp_id, experience, salary from cte2 where total>=0
union all
select emp_id, experience, salary from cte1

[NamanSeth]

Wonderful question Ankit. Here is my solution to that:

with senior_hire as(
select
*, sum(salary) over (order by salary asc) run_sum
from
candidates
where
experience = 'Senior'
qualify sum(salary) over (order by salary asc) <= 70000),

junior_hire as (
select
*, sum(salary) over (order by salary asc) run_sum
from
candidates
where
experience = 'Junior'
qualify sum(salary) over (order by salary asc) <= (70000 - (select max(run_sum) from senior_hire))

)

select * except (run_sum) from senior_hire
union all
select * except (run_sum) from junior_hire

[aaravkumarsingh]

with cte as(
select emp_id, experience, salary, sum(salary )over(partition by experience order by salary
rows between unbounded preceding and current row) as running_salary
from candidates
),
cte1 as(
select emp_id, experience, salary
from cte
where experience="Senior" and running_salary <= 70000),
cte2 as(
select emp_id, experience, salary
from cte
where experience="Junior" and running_salary<= (select 70000-sum(salary) from cte1 group by experience)
)
select * from cte1
union all
select * from cte2 order by emp_id;

[DE_Pranav]

with cte as
(
select *,
sum(salary) over(order by salary) as total
from candidates
)
select emp_id, experience, salary from cte
where total <70000
order by experience desc, salary asc

[skkholiya]

with exp_wise_sal as(
select *, sum(salary) over(partition by experience
order by salary
rows between unbounded preceding and current row)
rolling_sum from candidates),
selected_seniors as(
select * from exp_wise_sal where experience = "Senior" and rolling_sum <=70000
)
select * from selected_seniors
union
select * from exp_wise_sal where experience = "Junior"
and rolling_sum +
(select max(rolling_sum) from selected_seniors)<=70000;

[ashwingupta]

with cte as (
select *,
sum(salary) over(partition by experience order by salary) as cms
from candidates)
, cte1 as (
select * from cte
where experience = 'senior' and cms <70000
), cte2 as(
select * from cte1
union all
select *,
sum(salary) over(order by salary) as cms
from candidates
where experience = 'Junior'
), cte3 as(
Select *,
sum(salary) over(order by salary) as cms2
from cte2)
Select * from cte3
where cms2 < 70000

[BismarckWangkhem]

Hi, here is my approach:

with cte as
(select *,
sum(salary) over (partition by experience order by salary) running_sum from candidates),

cte2 as
(
select t.*,
sum(t.salary) over (order by experience desc, t.salary asc) final_running_sum from
(select * from cte
where running_sum <= 70000)t)

select emp_id, experience, salary from cte2
where final_running_sum <= 70000
order by emp_id

thank you :)

[ananthram]

select * from candidates

with cte as (
select*, sum(salary) over(order by salary ) as rnk from candidates )

select emp_id, experience, salary from cte
where rnk <=70000

[abb_raj]

Here is my different approach.
Ankit bhai I agree this is perfect problem to highlight 'CTE' usage.
Buz if we have more category of experience (eg. Senior, Mid, Junior), we need to create one more cte tb as category level inc.

See my approach⬇ :
with cte as (select *,
sum(salary) over(order by experience desc, salary asc rows between unbounded preceding and current row) first_rs_salary
from candidates
)
, cte2 as (
select *, sum(salary) over(order by experience desc, salary asc rows between unbounded preceding and current row) sec_rs_salary
from cte where not (experience='Senior' and first_rs_salary > 70000))

select * from cte2 where sec_rs_salary <= 70000;
Thanks bhai @Ankit!

[FootballWithAnkit]

Great qn
My sol is there is one more category named mid

with t1 as( select *, sum(salary) over(partition by experience order by salary asc) as cum from candidates
),
t2 as ( select *from t1 where experience='Senior' and cum<70000 ),
t3 as (select *from t1 where experience='Mid' and cum<(70000 - (select sum(salary) from t2)))
select * from t1 where experience='Junior' and cum<=(70000 - ((select sum(salary) from t2)+ (select sum(salary) from t3)) )
union all
select * from t2
union all
select * from t3

[anshulmehta]

with cte1 as
(Select *, sum(salary) over(partition by experience order by salary) as d,
70000 as budget from Candidates)
Select emp_id, experience, Salary from cte1
where d<=
(
Select
sum(salary) as remaining
from cte1 where d<=budget and experience='Senior')

[rawat]

My Way:

with cte as(
select *, sum(salary) over(partition by experience order by salary) as roll_sum
from candidates order by experience desc), cte2 as(
select emp_id, experience, salary from cte where roll_sum <= 70000 and experience = 'Senior'), cte3 as(
select * from cte2
union
select * from candidates where experience = 'Junior'), cte4 as(
select *, sum(salary) over(order by experience desc, salary) as roll_sum_salary
from cte3)
select * from cte4 where roll_sum_salary <=70000;