Amazon SQL Interview Question for Data Analyst Position [2-3 Year Of Experience ] | Data Analytics

In this video we are going to discuss a SQL interview problem where we need to find number of employees inside the hospital. I will solve this problem with 3 methods:

00:00 Understand the problem
02:35 Method 1 having
08:25 Method 2 joins
13:30 Method 3 Magic

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 hospital ( emp_id int
, action varchar(10)
, time datetime);

insert into hospital values ('1', 'in', '2019-12-22 09:00:00');
insert into hospital values ('1', 'out', '2019-12-22 09:15:00');
insert into hospital values ('2', 'in', '2019-12-22 09:00:00');
insert into hospital values ('2', 'out', '2019-12-22 09:15:00');
insert into hospital values ('2', 'in', '2019-12-22 09:30:00');
insert into hospital values ('3', 'out', '2019-12-22 09:00:00');
insert into hospital values ('3', 'in', '2019-12-22 09:15:00');
insert into hospital values ('3', 'out', '2019-12-22 09:30:00');
insert into hospital values ('3', 'in', '2019-12-22 09:45:00');
insert into hospital values ('4', 'in', '2019-12-22 09:45:00');
insert into hospital values ('5', 'out', '2019-12-22 09:40:00');

#sql #dataanalyst #interview #amazon

[hamdaniftikhar]

Hi Ankit!
Really interesting problem.

This is my solution with a window function.

WITH CTE AS (
SELECT *,
RANK() OVER (PARTITION BY emp_id ORDER BY time DESC) rnk
FROM hospital
)

SELECT *
FROM CTE
WHERE rnk=1
AND action='in'

[DeshaKannada]

;with cte4 as (
select *, row_number() over (partition by emp_id order by time desc) as activity
from hospital)

select * from cte4
where action='in' and activity=1;

i have found very short and smart answer:)...it works and i have tested.

[shivammandhan]

Hi Ankit,
Here's my solution - using concat and subqueries, I found this very intuitive
The logic is - for a person to be in the hospital last activity of employee should be 'IN'

select emp_id
from hospital
where concat(emp_id, time) in
(select concat(emp_id, max(time))
from hospital
group by emp_id)
and action = 'in'

[shivarajhalageri]

Assign rank (Partition by emp id order by date nd time desc ) then take out rank one of each employee id, , , and select employees id whose action is as In and count them that gives you total number of employees in hospital.

[Datapassenger_prashant]

I approached the problem statement with this sol:
with Latest_out_time as (
Select emp_id, max(time) as out_time from hospital
where action = 'out'
group by emp_id
),
latest_in_time as (
Select emp_id, max(time) as in_time from hospital
where action = 'in'
group by emp_id
)
Select i.emp_id
from latest_in_time i
left join
Latest_out_time o
on i.emp_id = o.emp_id
where i.in_time > o.out_time
or o.out_time is null;
However, like the first approach of having and the last magic solution too... It's all depends on person's thinking patterns how he sees the problem, and that's what reflects in the solution. so your videos always helps in thinking out of the box.

[wellywalker]

with cte as(
select *,
max(time)over(partition by emp_id) as last_entry
from hospital )
select count(*) as total_number_present
from cte
where time = last_entry and action = 'in'

[Akashsingh-redk]

Thanks for this explanation. I have one more way to do that.

with CTE as (
select emp_id, action, max(time) as time1,
case action when 'out' then 0
when 'in' then 1 else 0 end as total_in
from hospital
group by emp_id )
select emp_id, action, time1 from CTE where total_in=1;

[manojsrikanth]

Hi Ankit.. Really thanks to you.
My solution..
select emp_id, action, time from
(select emp_id, action, time, case
when count(emp_id) over (partition by emp_id) = 1 then 'p'
when max(time) over (partition by emp_id) - time = 0 then 'p'
end as rst
from hospital) q where rst = 'p' and action = 'in'

[_vanshika]

easy approach
with my_cte as (
select *,
dense_rank() over (partition by emp_id order by time desc) as rn
from hospital
)
select emp_id
from my_cte
where rn =1 and action = 'in'

[IswaryaMaran]

your videos are really helpful for my learning.Thanks a lot
Here's my approach:
select a.emp_id, a.timing as no_employees_inside from(select emp_id, max(time) as timing from hospital group by emp_id)a
inner join hospital on a.timing = hospital.time and a.emp_id = hospital.emp_id where action = 'in'

[mantisbrains]

select emp_id as no_of_people_inside from
(select emp_id, action, time from
(select *, row_number() over (partition by emp_id order by time desc)rn from hospital1)a
where rn = 1)a
where action ='in';

Thank you, Ankit !

[vibhavaribellutagi]

Hi Ankit,

Very interesting problem.

Here's my solution - which is similar to the last solution you mentioned in the video

select count(*) as emp_inside_hospital
from hospital h
join
(select
emp_id,
max(time) as max_time
from hospital
group by emp_id
order by emp_id) h1
on h.emp_id = h1.emp_id and h.time = h1.max_time
where action = 'in';

[prashansapunjabi]

Hi Ankit, I am solving these queries now! It's a cool problem to work with. My solution is below:

select * from hospital;

with in_time as
(select emp_id, max(time) as intime from hospital where action = 'in' group by emp_id),
out_time as
(select emp_id, max(time) as outtime from hospital where action = 'out' group by emp_id)

select count(it.emp_id) as no_of_employees
from in_time it left join out_time ot on it.emp_id = ot.emp_id
where it.intime > ot.outtime or ot.outtime is null;

[prathmeshudawant]

Hi Ankit,
Good Question.

This is my solution with a window function.

with cte as
(select *,
last_value(action) over(partition by emp_id range between unbounded preceding and unbounded following) as new_action
from hospital)

select distinct emp_id, new_action
from cte where
new_action='in';

[kunalmahadik]

Hi Ankit! This is my solution using subquery and window function.

SELECT emp_id
FROM
(SELECT
emp_id,
action,
DENSE_RANK() OVER (PARTITION BY emp_id ORDER BY time DESC) as rnk
FROM hospital) A
WHERE A.action = 'in' AND A.rnk = 1

[TejaswiniNandam]

Hi Ankit, Really amazing question.
Here is my solution:
with cte as
(select emp_id, action, LAST_VALUE(action) OVER (partition by emp_id ORDER BY time
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
as lstvl from hospital)
select count(distinct emp_id) as [count] from cte where lstvl='in'

[Srinivash_Sri]

Watching this in 2024! 🎉 I'm planning to work on your medium-complexity problems. 💪 So far, I've completed 3 problems in 1 hour, and I'm happy to continue this journey. 😊
Each video has some useful learning tricks! ✨

Here’s my answer:

SELECT emp_id
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY time DESC) AS rn
FROM hospital
) H
WHERE rn = 1 AND action <> 'out';

[dasoumya]

hello! ankit this is my solution


select *
from(select*, max(time) over (partition by emp_id)as last_time
from hospital) as A
where time = last_time and action= 'in'

[vipinyadav]

Hi Ankit,
Thanks for the informative video. I solved the from a different approach. Please have a look.

With cte as (
Select *,
OVER(partition by emp_id order by hospital."time" DESC) as Last_event
from hospital
) select distinct emp_id from cte where Last_event = 'in'

[vinothkumars]

with cte_ranked as
(
select *,
dense_rank() over(partition by emp_id order by time desc) as d_rank
from q3_hospital
)

select * from cte_ranked where d_rank=1 and action='in'


Glad i could see diff approaches. Thanks again, Ankit