Uplers SQL Interview Problem (Senior Data Analyst) | Includes 4 Test Cases
Вставка
- Опубліковано 4 жов 2024
- In this video we will discuss a SQL interview problem asked in Uplers interview for senior data analyst position. There are 4 test cases as well that we need to validate.
script:
Create table candidates(
id int primary key,
positions varchar(10) not null,
salary int not null);
test case 1:
insert into candidates values(1,'junior',5000);
insert into candidates values(2,'junior',7000);
insert into candidates values(3,'junior',7000);
insert into candidates values(4,'senior',10000);
insert into candidates values(5,'senior',30000);
insert into candidates values(6,'senior',20000);
test case 2:
insert into candidates values(20,'junior',10000);
insert into candidates values(30,'senior',15000);
insert into candidates values(40,'senior',30000);
test case 3:
insert into candidates values(1,'junior',15000);
insert into candidates values(2,'junior',15000);
insert into candidates values(3,'junior',20000);
insert into candidates values(4,'senior',60000);
test case 4:
insert into candidates values(10,'junior',10000);
insert into candidates values(40,'junior',10000);
insert into candidates values(20,'senior',15000);
insert into candidates values(30,'senior',30000);
insert into candidates values(50,'senior',15000);
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_...
#sql #dataanalyst #interview - Наука та технологія
Great video. The same question was asked to me in an interview few months back. Back then, I was not able to answer it properly, but thanks for the solution
Excellent!
I try to solve the problems from each and every video you upload. And I am able to solve this question in one go thanks to the concept similar to this you solved in the previous video you uploaded perhaps a few months back. Thanks for the concept.
Keep it up
Very useful. Please upload like this every day
This was definately an amazing video. Althought, I had an experience of solving similar approach thru your videos, but the test cases added bit more challenges and extra steps added to it, but it was easy to follow later steps as we alreaady built the main query and it was all about adjusting as per test cases.
my final query:
With Seniors_Selection as (
Select *, sum(salary) over(order by salary asc rows between unbounded preceding and current row) as Running_Salary_Sum from candidates
where positions = 'Senior'
)
, Seniors_Hiring_Budget as (
Select count(*) as Seniors, sum(salary) as Amount_used, case when Sum(salary) is null then 50000 else 50000-sum(salary) end as Amount_Remaining from Seniors_Selection
where Running_Salary_Sum
Thanks for the explanation
I have approached the problem like this : (Worked for all the test cases)
with cte1 as
(select *,sum(salary) over (partition by positions order by salary,id ) as rs from candidates)
,cte2 as
(select * from cte1 where rs
Thankyou for the great video.
with cte as(select b.*, sum(salary) over (partition by positions order by rn) total_amount from(
select c.*, row_number() over (partition by positions order by salary) rn from candidates c) b),
cte1 as (select * from cte
where (positions='senior' and total_amount
Ankit pausing the video I used 'isnull(sum(salary),0)' to handle null value for test case 3, it worked.
Hi Ankit sir,
with t as
(
select id, positions,salary,sum(salary)over(partition by positions order by salary) as sm from candidate1
order by positions desc
),
t1 as
(
select t.*, case when sm
always fan of your enjoyable content and well designed approach to any problem.
Glad to hear it!
As usual, great content. I just get how you find solutions. I guess it is talent, natural.
Thank you! Cheers!
with cte as(
select *, sum(salary) over( partition by positions order by salary,id) running_sal from candidates),
seniorCTE as(
select *
from cte
where running_sal
Sir, my query is as follows:
with cte_1 as
(Select id, positions, salary, SUM(salary) OVER(order by positions DESC, salary ASC rows between unbounded preceding and current row) as net_SUM
from
(Select *, SUM(salary) OVER(partition by positions order by salary, id rows between unbounded preceding and current row) as rnk
from candidates) xyz
where rnk
with cte as(
select *, row_number() over(partition by positions order by salary) as rn
from candidate3),
cte2 as(
select * ,
sum(salary) over(partition by positions order by rn) as sum_i
from cte
order by positions desc
),
cte3 as(
select * from cte2
where sum_i
select count(case positions when 'junior' then 1 end) 'Juniors', count(case positions when 'senior' then 1 end) 'Seniors'
from (select positions, sum(salary) over(order by case positions when 'senior' then 0 else 1 end, salary rows between unbounded preceding and current row) sm
from (select id, positions, salary
,sum(salary) over(partition by positions order by positions, salary rows between unbounded preceding and current row) ss
from candidates) t
where ss
My sol, Passing all the testcases :)
with cte as
(Select *, sum(salary) over (partition by positions order by salary,id)sum_sal from candidates)
select 'senior' as positions, count(id)as total_cnt from cte
where positions = 'senior' and sum_sal
Hi Ankit Brother
First of all many thanks for these 56 valuable videos so far, they are really helping us in our SQL journey.
In Addition I just wants to know your perspective upon will one will be able to crack the SQL technical interview if one have had prepared all these videos properly.
your take on that will be very insightful.
100 percent 😊
with cte as(
select *,
sum(salary) over(partition by positions order by salary) as total_sal
from candidates where positions='senior'
),
cte2 as(
select * from cte
where total_sal
with cte as(
with cte as(
select *,sum(salary) over(partition by positions order by salary,id) as sal from candidates)
,cte2 as(select
case when sal
Great Video,
This is my Query :
WITH running_sum as
(SELECT
*,SUM(salary) OVER(partition by positions order by salary) as running_sum
FROM practice.candidates
ORDER BY positions desc)
SELECT
SUM(CASE WHEN positions = 'senior' THEN 1 ELSE 0 END) AS 'Seniors',
SUM(CASE WHEN positions != 'senior' THEN 1 ELSE 0 END) AS 'Juniors'
FROM running_sum
WHERE running_sum
delete from candidates;
insert into candidates values(1,'junior',5000);
insert into candidates values(2,'junior',7000);
insert into candidates values(3,'junior',7000);
insert into candidates values(4,'senior',10000);
insert into candidates values(5,'senior',30000);
insert into candidates values(6,'senior',20000);
with running_cte as(
select
id,
positions,
sum(salary) over(partition by positions order by salary asc, id) as running_sal
from candidates
), senior_cte as (
select
count(id) as sen_count,
max(running_sal) as amount_senior
from running_cte
where positions = 'senior' and running_sal
with cte1 AS (
Select *, sum(Salary) over(partition by positions order by salary rows between unbounded preceding and current row) as rolling_sum
from candidates),
cte2 AS (
Select count(*) as Seniors, max(rolling_sum) as seniors_total_salary
from cte1
where positions = 'senior' and rolling_sum
Keep inspiring 🌟😍💓 great solution
Thank you so much 🤗
with cte as
(
select a.*,
sum(salary) over (partition by positions order by salary rows unbounded preceding ) running_balance
from candidates_4 a
),
cte2 as
(select q1.*, sum(salary) over (order by positions desc, salary rows unbounded preceding) expense from cte q1 where running_balance
Your approach was the best sir,
Direct and Simplified✅✅
WITH running_cte AS (
SELECT *,
SUM(salary) OVER (PARTITION BY positions ORDER BY salary ASC, id) AS running_sal
FROM candidates
)
, senior AS (
SELECT COUNT(*) AS seniors, COALESCE(SUM(salary),0) AS s_sal
FROM running_cte
WHERE positions = 'senior'
AND running_sal
with cte as (
select *, sum(salary) over (partition by positions order by salary,id asc ) as cum_sal
from candidatess
) , cte2 as (
select id,positions, salary,cum_sal
from cte
where positions='senior' and cum_sal
with cte as (
select *,
sum(salary)over(partition by positions order by salary, id) as running_total,
50000 - sum(salary)over(partition by positions order by salary, id) as remaining_balance
from candidates
),
cte1 as (
select count(*) as total_seniors
from cte
where positions = 'senior'
and remaining_balance > 0
),
cte2 as (
select * , (select coalesce(min(remaining_balance), 50000) from cte where positions = 'senior' and remaining_balance > 0) as balance
from cte
where true
and positions = 'junior'
),
cte3 as (
select count(*) as total_juniors
from cte2
where true
and balance - running_total >= 0
)
select (select total_seniors from cte1) as total_seniors, (select total_juniors from cte3) as total_juniors;
with cte as
(select positions, salary, 50000 - sum(salary) over(order by positions desc, salary asc) as diff_sal
from candidates
order by positions desc, salary asc
),
cte2 as
(
select positions, salary, 50000 - sum(salary) over(order by positions desc, salary asc) as diff_sal
from cte
where (positions = 'senior' and diff_sal >= 0) or positions = 'junior'
order by positions desc, salary asc
)
select coalesce(sum(case when positions = 'junior' then cnt end),0) as 'Juniors',
coalesce(sum(case when positions = 'senior' then cnt end),0) as 'Seniors'
from
(select positions, count(*) as cnt
from cte2
where (positions = 'senior' and diff_sal >= 0) or (positions = 'junior' and diff_sal >= 0)
group by 1) a1
@ankit here is my solution
with seniors as(
select *, sum(salary) over(order by salary ) as cum_sal
, 50000 - sum(salary) over(order by salary ) as delta
, rank() over(order by salary) as rnk
from candidates
where POSITIONS='senior'
)
, remain_budget as(
select
case when DELTA>=0 then DELTA else 50000 end
as DELTA from seniors
where (DELTA>=0 and rnk=(select max(rnk) from seniors))
or (DELTA=0
union
select * from juniors
where DELTA>=0
;
Thanks for the video , it was great..
Glad you enjoyed it!
Once Again Great Video Bro!!
hey @Ankit Bansal i have seen you vedio and solved in sql work bench i got correct result for test case 1 but not getting correct o/p for test case 2 and 3.
please show my code onces
with running_cte as
(select *,sum(salary) over(partition by positions order by salary asc,id) as running_sal
from candidates),
senior_cte as
(select count(*) as seniors,coalesce(sum(salary),0) from running_cte
where running_sal
My solution for test case 1
"select max(case when positions = 'junior' then cnt end) as Juniors,
max(case when positions = 'Senior' then cnt end) as Seniors from
(select positions,count(positions) cnt from(select positions,sum(salary) over(partition by positions order by salary,id)as total
from candidates_one)A
where total
Hi bro , can u explain about context switch......
Thank you...for solving my qstn
Any time
finished watching
Here's my sol:
with cte as (select *, sum(salary) over(partition by positions order by positions desc, salary,id) as runnin_sal from candidates) ,cte2 as (select count(*) as Seniors,max(runnin_sal) as runnin_sal from cte where runnin_sal
same question which you already covered with some test cases.
HI, Can you please tell Java developer interview process
If I am not wrong, you have solved similar question for one of the FAANG company before.
But nevertheless, good revision of all the concepts 🙏
Yes it's similar. Just different test cases here .
Can you give online classes for sql and informatica cloud?
positions='senior' not working on Mysql.
Try positions like 'senior' once
My Approach:
with senior_hires as (
select positions,
SUM(CASE WHEN cumuSalary
My Solution -:
WITH cte AS (
SELECT *, SUM(salary) OVER(PARTITION BY positions ORDER BY salary,id) running_sum
FROM Candidates);
SELECT
DISTINCT (
SELECT COUNT(*) FROM cte
WHERE positions='senior' AND running_sum
Hi Ankit
Aggregate function also called as Group function? what is the use of variance and standard daviation
Just for information
It would be much easier to use excel solver for this type of problem.
Test Case 3:
with cte as (
select *,
sum(salary) over(partition by positions order by positions desc, salary desc) cumulative,
(50000 - sum(salary) over(partition by positions order by positions desc, salary desc)) remain
from candidates3
order by positions desc, salary desc
),
cte1 as (
select count(*) seniors, coalesce(min(remain), 0) remain
from cte
where cumulative = (select remain from cte1)
)
select seniors, juniors
from cte1 join cte2
;
with cte as (
select count(*) as s , ifnull(max(s),0) as m from
(select positions , sum(salary) over(order by salary ) as s
from candidate where positions = 'senior')
as k where s < 50000 ) ,
cte2 as (
select count(*) as j from
(select positions , sum(salary) over(order by salary ) as sj
from candidate where positions = 'junior') as k
where sj
MySQL solution:
WITH cte_1 AS
(
SELECT *, SUM(salary) OVER(PARTITION BY positions ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS r_salary
FROM candidates
), cte_2 AS
(
SELECT id, positions, salary
FROM cte_1
WHERE positions = 'senior' AND r_salary
Hello Sir, How much DSA is required for Data Engineer Role, please reply, I am eagarly waiting for your reply
Basic to intermediate
oracle:
with senior_count as(
select count(id) s_id, nvl(sum(SALARY),0) senior_sal from(
select id,SALARY, sum(SALARY) over( order by rn) running_sum
from(
select id,POSITIONS,SALARY,row_number() over( order by SALARY)rn from candidates_test where POSITIONS ='senior'
))
where running_sum
WITH test AS (
SELECT
id,
positions,
salary,
SUM(salary) OVER (ORDER BY salary DESC, id) AS new_budget
FROM (
SELECT
*,
SUM(salary) OVER (PARTITION BY positions ORDER BY salary DESC, id) AS budget
FROM candidates
) cte
WHERE budget
Could you please make a vidio on this question asked in Mercedez Benz
Team-1 Team-2 Winner
A B A
B A B
C D D
A E A
N D N
B N B
WinTeam MatchesWon
TeamA 2
TeamB 2
TeamC 0
TeamN 1
TeamD 1
select COALESCE(sum(junior_flag),0) as junior,COALESCE(sum(senior_flag),0) as senior from (
select *,sum(salary) over (order by sal_rank1) as sal_sum1 from (
select *, row_number() over (order by positions desc,salary asc) as sal_rank1 from
(select *,dense_rank() over (Partition by positions order by salary) as sal_rank,sum(salary) over (Partition by positions order by salary) as sal_sum,
CASE WHEN positions = 'senior' THEN 1 END AS senior_flag,CASE WHEN positions = 'junior' THEN 1 END AS junior_flag
from candidate ) l
where sal_sum
Please check this out & let me know if it's flawed!
Thanks.
with cte as (
select *,
sum(salary) over(partition by positions order by positions desc, salary asc, id) as cumulative,
(50000 - sum(salary) over(partition by positions order by positions desc, salary asc, id)) remain
from candidates1
order by positions desc, salary
),
cte1 as (
select count(*) seniors, min(remain) remain from cte where positions = 'senior' and cumulative
WITH cte AS (
SELECT * FROM (
SELECT positions, salary, SUM(salary) OVER (PARTITION BY positions ORDER BY salary) AS rn_sum
FROM candidates
WHERE positions='senior'
) T
WHERE rn_sum
here i solve this quetions with 2 times cte function and case clause
with cte as(
select *,
sum(salary) over(partition by positions order by salary asc) as cumulative
from Emple),cte1 as
(select *from cte where positions in('senior','junior') and cumulative
Hi Rihan, thanks for the query. However, the logic seems to be a bit flawed. You are looking for candidates whose cumulative salary is
@@UnrealAdiAgree with you. The query does not even work for single test case
@@shafi123 please checkout my solution for the 1st test case! It is kinda similar to ankit's solution.
Let me know if you think if there is any flaw with it👍