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

[@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'

[@ankitbansal6]

This is really good 👍

[@hamdaniftikhar]

@@ankitbansal6 Thank you so much! I'm learning from the best afterall! 🙌🙌

[@Ravishanker.Muniasmy]

This is what I was thinking as well 👍

[@kingslyroche]

good one

[@tupaiadhikari]

Thanks Hamdan for your solution with Window Functions.

[@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.

[@SushilChauhan]

awsome bhai

[@shivammandhan4384]

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'

[@shivarajhalageri2513]

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.

[@Akashsingh-re9dk]

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;

[@manojsrikanth8663]

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'

[@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;

[@vibhavaribellutagi9738]

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';

[@wellywalker8397]

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'

[@ankitbansal6]

This is good 👌

[@vinothkumars7421]

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

[@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 !

[@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'

[@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'

[@ankitbansal6]

Thanks for posting 👏

[@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';

[@prathmeshudawant9252]

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';

[@_Samridhijain_]

select
count(distinct emp_id) as no_emp_present
from
(
select
emp_id,
action,
Last_value(action) over (partition by emp_id order by time asc rows between unbounded preceding and unbounded following) as last_status
from hospital_ank
) a
where last_status in ('in')

[@aryansingh-m6r]

Hi Ankit, I think the below is the simple and better solution:
with cte as (
select emp_id, action, row_number() over(partition by emp_id order by time desc) as rn
from hospital)
select count(emp_id) as no_of_emp_inside from cte
where rn = 1 and action = 'in';

[@Padmavati_P]

Hi Ankit,
Today I subscribed to this channel and started to solve the questions mentioned in the video.
Solution:
select b.emp_id,b.action,b.time from
(select emp_id,max(time)max_time
from hospital
group by emp_id )a
inner join hospital b on a.emp_id=b.emp_id and a.max_time=b.time
where b.action='in'

[@ankitbansal6]

Great keep going 😊

[@kunalmahadik4624]

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

[@udaybhansingh1658]

Hi Ankit we can do with help of row_number as below:
select * from
(select * , row_number() over(partition by emp_id order by time desc) rn from hospital ) a
where rn = 1 and action = 'in'

[@130_vanshika3]

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'

[@NavneetKaur-08]

Hello Ankit, thanks for the interesting problem and solution.
I solved it using window function and joins. Here's my version:
with cte as
(select distinct emp_id, max(time) over(partition by emp_id) as last_time
from hospital)
select h.emp_id, action, cte.last_time
from cte
join hospital h on cte.emp_id = h.emp_id and cte.last_time = h.time
where action = 'in';

[@sahilummat8555]

Hey Ankit , just another solution from my end
with cte as (
select *
,lag(action,1,action)over(partition by emp_id order by time) as prev_ac,
count(1) over (partition by emp_id)as cnt
from hospital),final as (
select *,
case when action ='in' and prev_ac='in' and cnt=1 then 1
when action ='in' and prev_ac='out' then 1
when action ='out' and prev_ac='out' then 0
else 0 end as flag
from cte)
select distinct emp_id from final where flag=1

[@vipinyadav8260]

Hi Ankit,
Thanks for the informative video. I solved the from a different approach. Please have a look.
With cte as (
Select *,
first_value(hospital."action") 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'

[@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'

[@ankitbansal6]

Good one 👍

[@learnanalyticswithMohit]

Hi Ankit, Really interesting problem. Here is my solution using subquery
select action,count(emp_id) from
(select emp_id,action,time
from hospital as h1
where time = (select MAX(time) from hospital h2 where h2.emp_id=h1.emp_id))
group by action

[@sunil-qx3vv]

Hi Ankit ...
I have solved with subquery.
select emp_id , action, time from hospital h2
where time = (select max(time) from hospital h1 where h1.emp_id = h2.emp_id group by emp_id)
and action = 'in'

[@rishirishi8135]

Thank you Ankit for providing script in the discription it's just helping alot

[@aayushkaul3963]

This is my personal favorite question and I always ask this in my interviews. But without giving the "action" column.. that makes it more interesting
Below is my solution:
Caveat: Assume the first entry is always "IN" entry (because you cannot have a first entry where you are already inside the building)
After that, it is just an ODD/EVEN game. Odd means inside, Even means Outside
SELECT SUM(inside) from (
select count(1)%2 as inside from #hospital group by emp_id)a

[@abhishekarora1]

Yeah, I was also thinking odd and even but then I saw the empID=5 case which has only out entry

[@Vicky-ne9mt]

hii i've just started learing, could u tell me "count(1)" which column u are doing count on, also could u just tell me the column names kindly

[@reachrishav]

@@Vicky-ne9mt count(1) is same as count(*), as long as you dont provide a column name inside count function, it's all the same. when you provide a column name inside the count function, it counts all the not null values in that column.

[@nikhilreddy4582]

Hi ankit! really interesting problems and here's my solution ( select count(emp_id) as no_of_people from(select *,row_number() over(partition by emp_id order by time desc)rn from hospital)a
where rn = 1 and action = 'in' )

[@sivahanuman4466]

Hi Ankit!
This is my solution
select a.emp_id,a.action,max(a.time) as last_updated_time from hospital a
join hospital b
where a.time>b.time
and a.action="in"
group by a.emp_id,a.action
order by emp_id asc;

[@Manjunath_07]

select emp_id,min(date_time) as patients_in_hospital from(
select emp_id,action,next_action,date_time from(
select *,coalesce(lead(action,1) over(partition by emp_id order by emp_id asc),"in") as next_Action from hospital) as a
where action="in" and next_action="in") as b group by emp_id;

[@tanmaysangwan2397]

Thanks for sharing such amazing content Ankit!
Here's my approach to the problem-
select
emp_id as emp_inside
from hospital
where (emp_id, time) in (select emp_id, max(time) as last_action_time from hospital group by emp_id)
and action = 'in';

[@amitkumar-g6d2z]

I appreciate your efforts for all the videos. Here is my answer to the question. Looking forward to hear from you if it is correct or not:
with cte as(
select emp_id,action,
ROW_NUMBER() over(partition by emp_id order by time desc ) rnk
from hospital)
select emp_id, action from cte
where rnk=1 and action='in';

[@learnanalyticswithMohit]

Hi Ankit, Really amazing solutions.
I have an alternate solution using windows function.
with CTE as (Select emp_id, action, last_value (action) over (order by time) as last_Action
from hospital )
select count(distinct emp_id) from CTE where last_Action = 'in'

[@MixedUploader]

Ankit I tried with rank func. below:
with cte as(select *,
rank() over(partition by emp_id order by time desc) as rnk
from hospital)
select count(emp_id) as total_employees
from cte
where rnk=1 and action='in'

[@130_vanshika3]

Hi! I solved it in 4 lines and after seeing the solutions wondering if am wrong !?
My approach is simple if intime - out time >0 that means person is still inside hence will do sum of intime - out time group by id and return when in time is positive
select emp_id
from hospital
group by emp_id
having sum(case when action ='in' then time else -1*time end) >0

[@subhankarmahanti4558]

Hi Ankit, Thanks to you. My solution is
select * from
(select *,row_number() over(partition by emp_id order by time desc) as rn from hospital) as h
where h.action='in'
and h.rn=1;

[@VISHALSINGH-jw2nn]

hi Ankit!!
i think i have an optimised solution...plz check using last_value() function and subquery:
select emp_id,min(time) as min_t,max(time) as max_t
from(select *,last_value(action) over(partition by emp_id order by time
rows between current row and unbounded following) as r
from hospital)t
where r='in'
group by emp_id

[@RahulRathore-wj9uy]

Hi Ankit Below is my solution
select emp_id,a.lead_col from
(
select *,lead(action,1,action) over(partition by emp_id order by time) as lead_col from hospital
) a
where a.lead_col='in'
group by emp_id,a.lead_col

[@codeset7810]

with window function it seems so easy
with cte as (select *,case when action = 'out' then 0
when action = 'in' then 1 end as total ,row_number() over(partition by emp_id order by time desc) as rnk from hospital)
select * from cte where rnk=1 and total =1;
or select sum(total) from cte where rnk=1;

[@navinahmed]

Hey Ankit,
Tried this, a little similar to the third solution, but easier:
WITH MAX_TIMES AS (
SELECT EMP_ID, MAX(TIME) AS CURRENT_TIME1 FROM HOSPITAL
GROUP BY EMP_ID
)
SELECT count(1) FROM MAX_TIMES A
LEFT JOIN HOSPITAL B ON A.CURRENT_TIME1 = B.TIME AND A.EMP_ID = B.EMP_ID
WHERE ACTION = 'in';

[@TarunDhimanOfficial]

Hi Ankit!
Thanks for the amazing content you are providing. You are truely a SQL Champion.

[@Brown_Munda_oo7]

WITH CTE AS (SELECT emp_id,action,ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY time DESC ) AS IO
FROM hospital)
SELECT emp_id
FROM CTE
WHERE IO =1 AND Action ='IN'

[@akashvishwakarma1317]

With
base as
(Select *,
max(time) over(partition by emp_id) as max_time
from hospital
)
Select
COUNT(
CASE
WHEN time=max_time and action = 'in'
THEN emp_id
END) as user_inside
from base

[@mounikabairi-o8f]

my simple one line answer is : with cte as (select *,row_number() over(partition by emp_id order by time desc) as rn from hospital) select emp_id from cte where rn=1 and action='in';

[@channeladventure9434]

Hi Ankit, another very small window function solution
with cte as (select *, max(time) over(partition by emp_id) as last_time from hospital)
select count(emp_id) from cte where time=last_time and action='in';
this basically checks last status of the person!

[@ankitbansal6]

Yup. Good one

[@ShivamGupta-ek4rd]

Hey Ankit, Below is my solution using joins
select count(*) as cnt from hospital as h
inner join
( select emp_id, max(time_) as max_time from hospital
group by emp_id ) as t
on h.emp_id = t.emp_id and h.time_ = t.max_time
where h.action_ = 'in'
group by action_

[@tridipmitra3278]

Hi Ankit ! very interesting problem. Here is my take on solution
with cte as (
select *, max(time) over (partition by emp_id) as max_time
from hospital
)
select emp_id,action,time from cte where time = max_time and action = 'in';

[@dibakarmandal2148]

1st of all thanks for this video series. 2nd I will not say my solution is better or not just posting it(quite similart to last one)
with cte as
(select emp_id, max(time) last_time from hospital group by emp_id)
select count(h.emp_id) as 'total_in' from cte c left join hospital h on c.last_time = h.time and c.emp_id=h.emp_id
where h.action ='in'

[@rishavvv6441]

with base as(
select *,
last_value(action) over(partition by emp_id ) as curr_flag
from hospital) ,base1 as(
select * from base where curr_flag='in')
select count(distinct emp_id) as no_of_employees from base1

[@sahilummat8555]

Another approach from my end :)
;with cte as (
select *
,LAST_VALUE(action)over(partition by emp_id order by time rows between unbounded preceding and unbounded following) as last_action
from hospital
)
select count(distinct emp_id) as number_of_person_in from cte
where last_action ='in'
or
;with cte as (
select *,
max(time)over(partition by emp_id ) as last_action_time
from hospital
)
select count(emp_id) as emp_cnt from cte
where time=last_action_time and action='in'

[@akashdeep5088]

The type of problems you provide is really top class ...thnxs a lot my this

[@rajunaik8803]

best approach:
with cte1 as (
select emp_id,case when action='in' then time end as in_time ,
case when action='out' then time end as out_time
from hospital
),cte2 as
(select emp_id,max(in_time) as in_time,max(out_time) as out_time from cte1 group by emp_id)
select * from cte2 where out_time is null or in_time>out_time

[@sahilsood2028]

with cte as (
select *,lead(action,1) over (partition by emp_id order by time ) as nextaction from hospital
)select emp_id, action from cte where nextaction is null and action = 'in'

[@pritishabiswas7429]

with cte as(
select * ,LAST_VALUE(action) over(partition by emp_id order by emp_id rows between unbounded preceding
and unbounded following) as last_action
from hospital
)
select distinct emp_id from cte
where last_action ='in'
group by emp_id

[@shivammadaan9498]

I OVERCOMPLICATED THE SOLUTION A BIT BUT IT WORKS ;-
with cte as(
select emp_id, action, row_number()over(partition by emp_id) as rn , count(action)over(partition by emp_id)as cn
from hospital)
select emp_id
from cte
where (action = 'in' and rn = 1 and cn%2 = 1) or (action = 'out' and rn = 1 and cn%2 = 0);

[@parth_pm16]

I've done using an inner join with same concept
SELECT DISTINCT RH.emp_id
from hospital as RH join hospital LH
on RH.emp_id = LH.emp_id
where (RH.emp_id = LH.emp_id and RH.time > LH.time and RH.action = 'in')
or RH.emp_id not in (select emp_id from hospital where action = 'out');

[@vaibhavverma1340]

Using lead window function:-
with cte as
(select emp_id, action, lead(action,1,action) over
(partition by emp_id order by time)lead_value
from hospital)
select count(case when lead_value = action and action = 'in' and lead_value = 'in' then 1 end) as total_no_of_people from cte

[@BismarckWangkhem]

Thank you for sharing Ankit.. :) Here is my approach:
with cte as
(select t.* from
(select *,
row_number() over (partition by emp_id order by time desc) n
from hospital)t
where t.n = 1)
select * from cte
where action = 'in'

[@ankitbansal6]

Good one 👍

[@BismarckWangkhem]

@@ankitbansal6 thank you :)

[@irannamented9296]

%sql
select emp_id from hospital
group by 1
having max(case when action = 'in' then time end) > max(case when action = 'out' then time end) or max(case when action = 'out' then time end) is null

[@mohdtoufique7446]

Hi Ankit.. Thanks for the content.. My approach
WITH all_actions as(
SELECT emp_id,RIGHT((string_agg(action,',')),3)AS all_actions
FROM hospital
GROUP BY emp_id)
SELECT emp_id
FROM all_actions WHERE all_actions IN('in',',in')

[@sahilsood2028]

with cte as (
select *,lead(action,1) over (partition by emp_id order by time ) as nextaction from hospital
)select emp_id, action from cte where nextaction is nullA

[@sanketchakane7745]

with cte as
(
select row_number() over (partition by emp_id order by time desc) as ronumber, * from hospital
)
select * from cte where ronumber = 1

[@naveend2475]

Hi Awesome Problem, I have used rank () function .. select ID,ACTION,TIME from (
select ID,ACTION,TIME,rank() over(partition by ID order by TIME desc) rnk from HOSPITAl) where ACTION='in' and rnk=1

[@ROHITKUMAR-lr7kb]

with cte1 as
(select *, ROW_NUMBER() over(partition by emp_id order by time) as rn
from hospital), cte2 as
(select emp_id, max(rn) as max_rn
from cte1
group by emp_id)
select c1.emp_id
from cte1 c1
inner join cte2 c2
on c1.emp_id = c2.emp_id and c1.rn =c2.max_rn
where action = 'in'

[@veerendramarni2605]

Select emp_id,
sum (case when action = ‘in’ then 1 else 0 end ) - sum (case when action = ‘out’ then 1 else 0 end )
from hospital
Group by emp_id

[@ank_kumar12]

-- hospital table
with cte as
(select *,
row_number() over(partition by emp_id) as rw
from hospital
),
cte2 as
(
select *,
max(rw) over(partition by emp_id) as max_val
from cte
)
select emp_id,action,time from cte2
where rw = max_val and action like 'in';

[@tupaiadhikari]

Thanks Ankit for the three beautiful solutions.

[@rajendramaharjan5018]

Hi Ankit, Below are my proposed solution
-- method 1
with cte as
(
select
*,
row_number()over(partition by emp_id order by time desc) as rn
from
#hospital
)
select
emp_id,
action,
time
from
cte
where
rn =1 and action= 'in'
-- Method 2:
with cte as
(
select
*,
max(time)over(partition by emp_id) as lasttime
from
#hospital
)
select
emp_id,
action,
time
from cte
where time = lasttime and action = 'in'

[@ankitbansal6]

Both are good

[@rajendramaharjan5018]

@@ankitbansal6Hey Ankit, Just to let you know that I am preparing for a BIE role and your videos have helped a lot in sharpening my SQL skills. Honestly, Every evening I do check if you have posted any SQL videos. You are doing an amazing job 👏 🙌 . Can you please start uploading the videos on Tableau too? Thanks!

[@ankitbansal6]

@@rajendramaharjan5018 sure

[@himanshuparihar9888]

we can use rank
for this problem
select sum(case when action = 'in' then 1 else 0 end ) as num_in from
(select * , rank() over(partition by emp_id order by time desc) as k from hospital) as k where k = 1 ;

[@anthonychilaka]

Day 3.
thank you Ankit for this scenario sql session.

[@ishika7585]

with cte as(
select *,last_value(action) over (partition by emp_id) as lv from hospital)
select distinct emp_id from cte
where lv= 'in'

[@civimunka3306]

with cte as(
select emp_id, action, `time`, row_number() over(partition by emp_id order by time desc) as r_no
from hospital)
select emp_id, action, `time` from cte where r_no=1 and action != 'out'

[@mohammadshahbaz3287]

@ankit here is my sol:
select count(distinct case when action='in' then emp_id end) as cnt from
(select distinct *, rank() over(partition by emp_id order by time desc) as rnk
from hospital)
where rnk=1
;

[@DanishAnsari-hw7so]

My solution using window function:
select emp_id from
(select emp_id, action, time, dense_rank() over (partition by emp_id order by time desc) row_num from hospital) A
where A.row_num in (1) and A.action in ("in");

[@gaurav5010]

An approach with window function : -
With cte as
(
Select
distinct
emp_id,
last_value(action) Over(partition by emp_id Order by time asc Range between Unbounded preceding and unbounded Following) as lval
From
hospital
)
Select
Sum(case when lval='in' Then 1 Else 0 End) as Total_inside
FROm
cte

[@vandanaK-mh9zo]

select * from (Select
distinct
emp_id,
last_value(action) Over(partition by emp_id Order by time asc Range between Unbounded preceding and unbounded Following) as lval
From
hospital) where lval = 'in';

[@vandanaK-mh9zo]

good one

[@rohanahuja9974]

select emp_id, row_num from
(select emp_id, action, row_number() over (partition by emp_id order by time desc) as row_num
from hospital ) a
where row_num = 1 and action = 'in'

[@ashrafelnagar8189]

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

[@bhuvesh09]

using a subquery:
select * from hospital h1 where
time =(Select max(time) from hospital h2 where h1.emp_id=h2.emp_id ) and action='in'

[@VedanshiReddy131]

Hi,Below is my solution similar to your 3rd solution.
SELECT COUNT([Action]) AS CntOfInEmps FROM (
SELECT H.Emp_id,H.Action,H1.MaxTime FROM Hospital H
JOIN (
SELECT emp_id,MAX(time) AS MaxTime FROM hospital
GROUP BY emp_id)H1 ON H.emp_id = H1.emp_id AND H.time = H1.MaxTime
) AS A
WHERE [Action] = 'In'
One suggestion,use upper case for reserved keywords in query.That is one of the best practices.

[@ranganroy8494]

Hi Ankit,
Kindly look into my below solution.
select count(x.emp_id) as 'number_of_employees_inside_hospital' from (select h.*, row_number() over(partition by h.emp_id order by h.time desc) as r_num from hospital h) x where x.action = 'in' and x.r_num = 1;

[@chgeetanjali7919]

My solution
select sum(case when action='out' then 0 else 1 end) as no_of_employees from
(select * from (select *,row_number()over(partition by emp_id order by time desc)rm from hospital)A
where rm=1)B ;

[@puneetnaik8719]

Thanks for the question. Solved using below approach which checks for latest time of emp_id and filters the emp_id if the action is "IN"
WITH emp_max AS (
SELECT
emp_id,
max(time) AS max_time
FROM
hospital
GROUP BY
emp_id
)
SELECT
h.emp_id
FROM
hospital h
JOIN emp_max e ON h.emp_id = e.emp_id
AND h.time = e.max_time
AND action = 'in'

[@ankitbansal6]

Thats my 3rd method. Good one 👍

[@vijaygupta7059]

with cte as
(
Select *
, row_number() over (partition by emp_id order by time desc) as new
from hospital
)
Select * from cte
where new = 1 and action ='in';

[@vamshi8228]

select emp_id ,action, max from(
select emp_id , action, time ,max(time) over(partition by emp_id) as max from hospital) as x
where action = 'in' and time = max
group by emp_id , max

[@mathman298]

with cte as(select DISTINCT * from
(select *, Lead(action ,1) over(partition by emp_id order by time) as second ,
Lead(time,1) over(partition by emp_id order by time) as out_time
from hospital ) as a
where action = 'in'
)
select count(emp_id) as 'total number of emp inside' from cte where second is null

[@ATHARVA89]

heres my solution
with some_tbl as (
select *, rank() over(partition by emp_id order by time desc ) as outtime from hospital
)
select * from some_tbl where outtime=1 and action ='in'

[@chahatjain3745]

with cte as (Select emp_id, action,
case when time=max(time) over(partition by emp_id) and action="in" then 1 else 0 end as rn
from hospital)
Select distinct emp_id from cte where rn=1;

[@shwetasaini6892]

My version -
select
emp_id,
action
from (
select
*,
dense_rank()over(partition by emp_id order by time desc) as rn
from hospital ) as x
where x.rn = 1
and action = 'in'

[@gyanubhai86]

select count(1) from (
select a.*,
row_number() over ( partition by emp_id order by time desc) rn
from hospital a
) b where rn=1 and action ='in'

[@saib7231]

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

[@akshaysharma557]

With temp as (Select empid, Case when action='in' then 1 else -1 end act
From hospital)
Select empid from temp
Group by empid
Having sum(act) > 0

[@akshaysharma557]

Another one will be using partition by empid order by time desc
And then using this as
Select * from temp where dr=1 and action ='in'

[@ankitbansal6]

If first action is out and second is in then the sum will be zero. You need to handle the scenario when number of in and out are same and last action is in.

[@jeffbauman7567]

with cte as (
select
EMP_ID, ACTION,time, row_number() over(partition by emp_id order by time desc ) RN
from hospital)
select * from cte where rn =1 and ACTION='in'

[@praneethvarma8208]

with k
as
(
select * , dense_rank() over (partition by emp_id order by [time] desc) as rnk
from #hospital
)
select emp_id
from k
where rnk = 1 and [action] = 'in'

[@SubhamKumar-or8vc]

using max
with cte as (select emp_id, action, time,
max(time) over(partition by emp_id) max_time
from hospital)
select count(1) from cte where time = max_time and action = 'in'

[@swethathiruppathy9973]

Hi Sir,
This 4 line code works, please do verify and confirm
select emp_id
from hospital
group by emp_id
having action="in" and max(time)

[@sandeepagarwal7155]

I guess this would be a simple solution to this :
select count(emp_id) from (
select *,
rank() over(partition By emp_id order by time desc) rnk
FROM hospital)
where rnk = 1
and action = 'in'

[@gothams1195]

select distinct emp_id ,n from (select *,
first_value(action) over(partition by emp_id order by time desc ) as n
from hospital)a where n='in'

[@ankitdhar3692]

with cte as (
select * from
(select *,dense_rank() over(partition by emp_id order by time desc) as drn
from hospital) as c
where c.drn=1)
select
sum (case when t.action='in' then 1 else 0 end) as [Number of Patients]
from cte as t;