/*
Amazon SQL Interview Question

Find the total number of people present inside the hospital
*/
    
-- Schema Setup
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');

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

SELECT
    emp_id AS emp_inside
FROM ranked_timing
WHERE rnk = 1
AND action = 'in';