Tricky - SQL Query - Interview - find count of employees inside the office 1 PM on each day - Amazon

Emloyee punch In/Out timing Analysis

Create Table emp_punch_in_out(
empid int,
in_out varchar(5),
punch_time datetime
);

truncate table emp_punch_in_out;

insert into emp_punch_in_out values(1, 'IN', '2024-08-01 09:00');
insert into emp_punch_in_out values(1, 'OUT', '2024-08-01 13:00');
insert into emp_punch_in_out values(1, 'IN', '2024-08-01 15:00');
insert into emp_punch_in_out values(1, 'OUT', '2024-08-01 18:00');
insert into emp_punch_in_out values(1, 'IN', '2024-08-02 11:00');
insert into emp_punch_in_out values(1, 'OUT', '2024-08-02 16:00');
insert into emp_punch_in_out values(1, 'IN', '2024-08-02 17:00');
insert into emp_punch_in_out values(1, 'OUT', '2024-08-02 20:00');

-- emp 1 was in office
-- on 01-Aug from 9:00 to 13:00 and from 14:00 to 06:00
-- on 02-Aug from 11:00 to 16:00 and from 17:00 to Next day 3-Aug 04:00
-- ==============================================================

insert into emp_punch_in_out values(2, 'IN', '2024-08-01 08:00');
insert into emp_punch_in_out values(2, 'OUT', '2024-08-01 12:00');
insert into emp_punch_in_out values(2, 'IN', '2024-08-01 13:00');
insert into emp_punch_in_out values(2, 'OUT', '2024-08-01 18:00');
insert into emp_punch_in_out values(2, 'IN', '2024-08-02 09:00');
insert into emp_punch_in_out values(2, 'OUT', '2024-08-02 13:00');
insert into emp_punch_in_out values(2, 'IN', '2024-08-02 14:00');
insert into emp_punch_in_out values(2, 'OUT', '2024-08-02 19:00');

-- emp 2 was in office
-- on 01-Aug from 8:00 to 12:00 and from 13:00 to 06:00
-- on 02-Aug from 09:00 to 13:00 and from 14:00 to 19:00
-- ==============================================================
-- ==============================================================

insert into emp_punch_in_out values(3, 'IN', '2024-08-01 09:00');
insert into emp_punch_in_out values(3, 'OUT', '2024-08-01 18:00');

-- emp 3 was in office
-- on 01-Aug from 9:00 to 18:00
-- on 02-Aug emp 3 was absent
-- ==============================================================