Microsoft SQL Interview Question for Data Engineer Position | Data Analytics 

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

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

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

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

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

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 ;

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)

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

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

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

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

Thank you for another great video, i had solved the same question, but the output required in that question was just the count of seniors and juniors, instead of the actual rows. Your solution is more optimised than what I wrote. Thank you for the video once again.

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

Make 2 ctes one senior employees and one junior employees, find the running sum in both the cases then take out senior employees whose running sum is less than 70000 and find the last running sum value of the senior employees that we have taken out subtract that from 70000 and and it will give some x then take out the running sum values of junior cte table who's values less than x, then combine these two with union all. Correct me if I am wrong @ankit 🙌

Hi Ankit can you have short series on data-modelling ? that will be very helpful.

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

Solved without any solutions: with cte1 as (select *, sum(salary) over(partition by experience order by salary rows between unbounded preceding and current row) as rn_sum from candidates), cte2 as ( select *,max(rn_sum) over(partition by experience) as max_rn_sum from cte1 where experience='Senior' and rn_sum

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

Hi Ankit, I've completed your "Leetcode SQL Hard problem" playlist. Thank you for making content to help the Data enthusiast like me. I got to learn so many new ways to think to solve the questions which I don't think of previously. BTW I've purchased your SQL course also.

Dear Ankit.. Many thanks for the clean code with perfect logic !! .. Keep up!! :)

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

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

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

Glad I found your channel man. Thank you so much for these questions!

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

Amazing as u provide raw data for practice...

Please check : with cte as( select *,sum(salary) over(partition by experience order by salary asc) total_salary from candidates) ,senior_salary as ( select emp_id,experience,salary from cte where experience = 'senior' and total_salary

awesome..explained very well!!

mysql solution with seniors as ( select *, sum(salary) over(order by salary asc rows between unbounded preceding and current row) as rolling_sum from candidates where experience = 'Senior' order by salary asc ), juniors as ( select *, sum(salary) over(order by salary asc rows between unbounded preceding and current row) as rolling_sum from candidates where experience = 'Junior' order by salary asc ), left_amount as ( select max(rolling_sum) as amount from seniors where rolling_sum < 50000 ), selected_junior as ( select * from juniors,left_amount where rolling_sum < amount ) select emp_id ,experience from seniors where rolling_sum < 50000 union select emp_id ,experience from selected_junior

That’s a great question and explanation. Appreciate it

SELECT * FROM interview_pre.candidates; with cte as( select *,sum(salary) over(partition by experience order by salary asc) as running_Sal from candidates), cte1 as (select * from cte where experience='Senior' and running_sal

Another good question with different approach

with cte as( SELECT *,sum(salary) over(partition by experience order by salary) as r_summ, 70000 as max_sal FROM candidates), senior as( select *,(max_sal-r_summ) remaning_b from cte where experience = 'Senior' and r_summ < 70000), junior as( select * from cte where experience= 'Junior' and salary < (select max(r_summ) from senior) ), final as( select emp_id,experience,salary from senior union all select emp_id,experience,salary from junior) select * from final order by emp_id

There should be a COALESCE to verify if the running sum for juniors is the total budget, the comparison for juniors wouldn't get evaluated due to NULL and hence the output wouldn't show any row even if the juniors were hired. where experience = 'Junior' and run_sal

with cte as( select *,sum(salary) over(order by salary) as sen_sal from candidates where experience = "Senior"), cte2 as( select *,max(sen_sal) over() as rem_budget from cte where sen_sal

Hi Ankit solved it using first_value and last_value window function plz have a look and tell if its ok or not:- With cte as ( Select *, first_value(recipientid) over(partition by callerid,cast(datecalled as date) order by datecalled) as first_day_call, last_value(recipientid) over(partition by callerid,cast(datecalled as date) order by datecalled rows between unbounded preceding and unbounded following) as last_day_call from phonelog ) Select distinct callerid,recipientid from cte where recipientid=first_day_call and recipientid=last_day_call;

Hi Ankit, Please find my solution :) with rollingSum as ( select *, sum(salary) over(partition by experience order by salary asc rows between unbounded preceding and current row) as rolling_sum from candidates ) , seniorEmp as ( select * from rollingSum where rolling_sum < 70000 ), remainingAmount as ( select *, sum(rolling_sum) over(partition by experience) as balanceAmount from seniorEmp where experience = 'Senior' ) select distinct se.emp_id, se.experience, se.salary from seniorEmp se inner join remainingAmount ra on se.rolling_sum < ra.balanceAmount

Here is my solution: with cte as ( select * , sum(salary) over(partition by experience order by salary) as rolling_salary from candidates ) , cte2 as ( select *, sum(salary) over( order by (case when experience='Senior' then 0 else 1 end), rolling_salary) as rolling_salary2 from cte where rolling_salary

with CTE AS ( select *, sum(salary) over(partition by experience order by salary) as running_sal from candidates ), CTE2 AS ( select *, row_number() over(order by running_sal desc) as seniors_tot_sal from CTE where experience='Senior' and running_sal

Thank you so much for the video and a wonderful explanation 😇

Btw you were telling about duplicate and use of unbound preceeding at end of vidoe. Could you please explain

My Solution: ;with cte as ( select *,sum(salary) over(partition by experience order by emp_id) as RN from candidates ), cte2 as ( select * from cte where experience='Senior' and RN

Thanks you for good information giving

Hi Can you please make video on difference between group by and partition by

with CTE as ( select *, case when sum(salary) over(partition by experience order by salary) < 70000 and experience ='Senior' then emp_id end as Senior_group from candidates), amount_used as ( Select Sum(salary) as Total_Used_Amount from CTE where Senior_group is not null), Cte2 as ( select *, case when sum(salary) over(partition by experience order by salary) < 70000 - (Select Total_used_amount from amount_used) and experience ='Junior' then emp_id end as junior_group from candidates) Select emp_id, experience, salary from Cte2 where Junior_Group is not Null union all select emp_id, experience, salary from CTE where Senior_group is not null

Please do the videos on stored procedures

Great

Awesome one bro

Hi Ankit, first of all thanks a lot for the exclusive content you provide on your channel !! Tremendous job Here is my approach to the problem: with one as ( select *,sum(salary) over(partition by experience order by salary) as r_sum from candidates ), seniors as ( select * from one where experience='Senior' and r_sum

with senior_candiates as (select *,sum(salary) over(order by salary) as rolling_sal from microsoft_candidates where experience = 'Senior'), selected_seniors as (select * from senior_candiates where rolling_sal

My Solution: with cte1 as (select *,sum(salary)over(partition by experience order by salary) as running_budget from candidates where experience='senior'),cte2 as (select *,rank()over(order by running_budget desc) as budget_rank from cte1 where running_budget

Great job 👍👍

with sen as(select emp_id,experience,salary,run from (select *,(sum(salary) over (order by salary)) as run from candidates where experience='senior') b where run jun as (select emp_id,experience,salary,run from (select *,(sum(salary) over (order by salary)) as run from candidates where experience='junior') a where (run+(select max(run) from sen)) select emp_id,experience,salary from jun union all select emp_id,experience,salary from sen;

this was good video

create temp table uj as (select * from (select * , sum(salary) over (partition by experience order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as following_salary from candidates where experience = 'Senior') where following_salary < 70000); select emp_id,experience,salary from uj union all select emp_id,experience,salary from( select * , sum(salary) over (partition by experience order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as following_salary from candidates where experience = 'Junior') where following_salary < (select 70000 - max(following_salary) from uj)

Hi, thank you for the video, being honest I have never used aggregation with OVER functions as since DAX came out, this can be easily solved on the reporting side. Now, my question is very punctual. Would you recommend to run this in large datasets? How light is to apply this when you are talking about millions of rows?

My Solution: with t1 as (select *,sum(salary) over(order by salary) as sm from candidates where experience='Senior'), t2 as (select emp_id,experience,salary from t1 where sm

Brother can you make a case study in SQL so that it will helpful like my other brothers who are interested. Thanks

;with cte as ( SELECT *, sum(salary) over(order by salary rows between unbounded preceding and current row) as rol_sum from candidates where experience = 'Senior' ), cte1 as (SELECT 70000- sum(salary) as rem from cte where rol_sum

brilliant

WITH cte AS ( SELECT *, SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS r_spent FROM candidates ) SELECT * FROM cte WHERE r_spent

Question was interesting but bit tricky

Solution: ===================== with cte1(emp_id,experience,salary,rolling_sum) as (select emp_id , experience ,salary , sum(salary) over(order by salary asc rows between 6 preceding and 0 following) as rolling_sum from candidates where experience = 'Senior'), cte2 as ( select emp_id , experience , salary from cte1 where rolling_sum

with a as ( select *, min(df) over() budget_2 from ( select *, 70000 - sum(salary) over(order by salary, emp_id) df from candidates where experience = 'Senior') t where df >=0 ) select emp_id, experience, salary from ( select *, (select min(budget_2) from a) - sum(salary) over(order by salary, emp_id) df from candidates c where experience = 'Junior') t where df>=0 union select emp_id, experience, salary from a

with cte as( select candidates.*,SUM(salary)OVER(PARTITION BY experience ORDER BY salary) as es FROM candidates ),cte1 as( select emp_id,experience,salary,es FROM cte where es

HI Ankit, I would like to know if there are any python modules in particular to prepare for a data engineer role.. Thanks

I have tried like below and not sure its optimized with cte1 as ( select *,Sum(Salary) over (partition by experience order by salary) as RT from candidates) , cte2 as( select c1.*,max(c2.rt) as rt2,c1.rt+max(c2.rt) as Total from cte1 c1 left join cte1 as c2 on c2.experience='Senior' and c2.rt

with cte1 as ( select emp_id,salary,experience from ( select emp_id, experience, salary, sum(salary)over(partition by experience order by salary asc) roll_sum, dense_rank()over(partition by experience order by salary asc) rnk from candidates order by emp_id ) where experience = 'Senior' and roll_sum

We can also achieve this output by using temp table Am i right?

Hi Ankit, Solution dekhke to asaan lgta hai..but khud try krta hu to nahi hota. Please help..kaise kru SQL strong so that I dont have to see solutions

with cte as ( select emp_id , experience,salary , sum(salary) over(partition by experience order by salary) as total_salary_sum from candidates ) , cte1 as ( select emp_id, experience ,salary,total_salary_sum from cte where total_salary_sum < 70000 and experience = 'Senior' ), cte2 as ( select emp_id,experience, salary From cte where total_salary_sum < 70000-(select max(total_salary_sum) from cte1 ) and experience = 'Junior') select * from cte2 union select emp_id, experience ,salary from cte1;

with senior_hires as ( select emp_id, experience, salary, rsum from ( select emp_id, experience, salary, sum(salary) over (order by salary) as rsum from candidates where experience = 'Senior' ) where rsum

with s as (select *,Sum(salary) over (order by experience desc,salary asc) running_sum from candidates where experience ='Senior') ,l as ( select emp_id,experience,salary,sum(salary) over (order by experience desc,salary asc) rs from ( select * from s where running_sum

This is my solution: WITH CTE1 AS ( SELECT emp_id, experience,senior_sum as salaries FROM (SELECT *, sum(salary) over (partition by experience order by salary) as senior_sum from candidates WHERE experience = 'Senior') AS e WHERE senior_sum < 70000), CTE2 AS ( SELECT emp_id, experience,junior_sum as salaries FROM (SELECT *, sum(salary) over (partition by experience order by salary) as junior_sum from candidates WHERE experience = 'Junior') AS e WHERE junior_sum < (SELECT salaries FROM CTE1 ORDER BY SALARIES DESC LIMIT 1)) SELECT * FROM CTE1 UNION SELECT * FROM CTE2;

I was trying to play around in SQL and getting a syntax error here. Could anyone tell me the issue here pls WITH cte AS ( SELECT *, SUM(salary) OVER(PARTITION BY experience ORDER BY salary ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_sal FROM candidates_sep ), seniors AS (SELECT * FROM cte WHERE experience = 'Senior' AND sum_sal

👍👍

with cte1 as (select *,70000-max(cum_sum) over() remain from (select *,sum(salary) over(order by salary) cum_sum from candidates where experience="senior") a where cum_sum

Bhai bhai bhai 👨

WITH cte AS ( SELECT *, SUM(salary) OVER(PARTITION BY experience ORDER BY salary) AS cf FROM candidates ) , Senior AS ( SELECT emp_id,experience,salary FROM cte WHERE cf

How many hacker rank dsa questions should do for fresher data engineer at faang?

100%

your answer is wrong .... at 7:58 there are 4 employees 2 senioprs and 2 juniors with total salry coming to be 87000 (10k + 25k + 16k + 36k) ... the answer should not contain the guy with 25k salary and that totals to 62k then ... Find the below code for the same ... I have used Postgres WITH total_budget AS ( SELECT 70000 ) , running_sals AS ( SELECT emp_id, experience, salary , SUM(salary) OVER( PARTITION BY experience ORDER BY salary ASC, emp_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_sal FROM candidates ) , seniors AS ( SELECT * FROM running_sals WHERE experience='Senior' AND running_sal

HI , I have tried to use Recursive CTE approach IN MYSQL for my learning with recursive base as (select *,rank() over (partition by experience order by salary asc) as rnk_within from candidates) ,base_senior_recruitment as ( select emp_id,salary,experience,salary as budget, rnk_within as rnk from base where experience = 'Senior' and rnk_within = 1 UNION ALL select t2.emp_id,t2.salary,t2.experience,t1.budget + t2.salary as budget,t2.rnk_within as rnk from base_senior_recruitment t1 join base t2 on t2.rnk_within = t1.rnk+1 and t1.budget + t2.salary < 70000 and t2.experience = 'Senior' and t1.emp_id != t2.emp_id ), budget_left as ( select 76000 - sum(salary) as budget_left from base_senior_recruitment ), base_junior_recruitment as ( select emp_id,salary,experience,budget_left - salary as left_money, rnk_within as rnk from base,budget_left where experience = 'Junior' and rnk_within = 1 and salary < budget_left UNION ALL select t2.emp_id,t2.salary,t2.experience,t1.left_money - t2.salary as left_money,t2.rnk_within as rnk from base_junior_recruitment t1 join base t2 on t2.rnk_within = t1.rnk+1 and t2.salary < t1.left_money and t2.experience = 'Junior' and t1.emp_id != t2.emp_id ) select emp_id,salary,experience from base_senior_recruitment UNION ALL select emp_id,salary,experience from base_junior_recruitment

Here is my solution to above problem: with cte as( SELECT *, sum(salary) over (Partition by experience order by salary) as rolling_sum from candidates ), cte2 as( SELECT *, 70000-sum(salary) over (Partition by experience) as rem_salary FROM cte where rolling_sum

my solution with watching yours with cte_s1 as( select *, sum(salary) over(order by salary asc) as senior_sal_cum from candidates where experience = 'Senior' order by salary asc ), cte_j1 as( select *, sum(salary) over(order by salary asc) as junior_sal_cum from candidates where experience = 'Junior' order by salary asc ) select emp_id, experience, salary from cte_s1 where senior_sal_cum

WITH cands AS ( SELECT e.emp_id,e.salary,e.experience,e.SumCumulative,MAX(e.BudgetRemainder) OVER () AS BudgetRemainder FROM ( SELECT t.emp_id,t.salary,t.experience,t.SumCumulative,70000-MAX(t.SumCumulative) OVER() AS BudgetRemainder FROM ( SELECT c.emp_id, c.salary, c.experience, SUM(c.salary) OVER(PARTITION BY c.experience ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumCumulative FROM candidates c WHERE 1=1 AND c.experience='Senior' ) t WHERE 1=1 AND t.SumCumulative