SQL Interview Question | TCS | 18 #dataengineering | #dataanalyst | #powerbi | #sql #dataengineer

If you like this video please
like share , subscribe and comment down below..
if you have any suggestion or doubt this video.

create table call_start(ph_no varchar(10),start_time datetime);
insert into call_start values
('contact_1','2024-05-01 10:20:00'),
('contact_1','2024-05-01 16:25:00'),
('contact_2','2024-05-01 12:30:00'),
('contact_3','2024-05-02 10:00:00'),
('contact_3','2024-05-02 12:30:00'),
('contact_3','2024-05-03 09:20:00')

create table call_end(ph_no varchar(10),end_time datetime);
insert into call_end values
('contact_1','2024-05-01 10:45:00'),
('contact_1','2024-05-01 17:05:00'),
('contact_2','2024-05-01 12:55:00'),
('contact_3','2024-05-02 10:20:00'),
('contact_3','2024-05-02 12:50:00'),
('contact_3','2024-05-03 09:40:00')

[ANUSHRIDAFE]

Thank you so much for sharing with dataset ... making more interesting for me

[priyarane]

with cte1 as(
select *, row_number() over(partition by ph_no order by start_time) as r_no
from call_start),
cte2 as(
select *, row_number() over(partition by ph_no order by end_time) as r_no
from call_end)
select cte1.ph_no, cte1.start_time, end_time, timediff(start_time, end_time) as duration
from cte1 inner join cte2 on cte1.r_no=cte2. r_no and
cte1.ph_no=cte2. ph_no;