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

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

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

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