Solving an INTERMEDIATE SQL Interview Query - 29 |

[@krishna35997]

today is last day of this magnificent series......but please continue your work in one or other way......because many people are relying on you for clearing the interviews or to better their work performance .......personally beyond this I addicted to your videos......until I watch your videos i wont sleep for the day.......Please continue your great work .......and inspire the generations to come

[@techTFQ]

Thank you Krishna ❤
Truly appreciate your kind words 🙏

[@Satish_____Sharma]

Here is MY SOLUTION USING MYSQL
with cte as (select times, status,lag(status) over (order by times) as leads
from login_details)
,cte1 as (select times, status,sum(case when status='on' and leads='off' then 1 else 0 end) over (order by times) as flag from cte)
select min(case when status='on' then times end) as logon_time
,min(case when status='off' then times end) as logout_time
,round(cast(timediff(min(case when status='off' then times end),min(case when status='on' then times end)) as decimal)/100) as duration
from cte1 group by flag

[@monasanthosh9208]

More Easy Version of this Query
With CTE as
(Select *,Lag(Status) over (Order by times) as Leads from Login_details),
CTE1 as
(Select *,sum(Case When Status="on" and Leads="off" then 1 else 0 end) over (order by Times) as Seg from CTE ),
CTE2 as
(Select *,Min(Case When Status="on" then Times end) as Login_Time,
min(Case When status="off" then times end) as Logoff_Time
from CTE1 group by seg order by Times),
CTE3 as
(Select Login_time,Logoff_time,timediff(Logoff_time,login_time) as Duration from CTE2)
Select Login_time,Logoff_time,extract(minute from duration) as Duration from CTE3;

[@Satish_____Sharma]

Certainly! Adding more common table expressions (CTEs) can impact query performance because each CTE creates an intermediate result set that needs to be computed. Therefore, minimizing the number of CTEs can generally lead to better query performance, as it reduces the computational overhead.
Thanks for your query

[@monasanthosh9208]

you are the easiest SQL guide i have ever seen, I am following this 30 days challenge with your Point of Query Stmts
Thank you so much to make me learn sql in easy way

[@kedarwalavalkar6861]

with cte as (
select *
,case when status = 'on' and lag(status,1,status) over(order by times asc) = 'off' then 1 else 0 end as flg
from login_details
)
,cte2 as (
select *
,sum(flg) over(rows between unbounded preceding and current row) as parts
from cte
)
select
min(case when status = 'on' then times else null end) as log_on
,min(case when status = 'off' then times else null end) as log_off
,timestampdiff(minute,min(case when status = 'on' then times else null end),min(case when status = 'off' then times else null end)) as diff
from cte2
group by parts

[@DEwithDhairy]

PySpark 30 Days Challenge of these problems :
th-cam.com/play/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0.html

[@vikramjitsingh6769]

Easier, less line code would be - select min(times), max(times), count(a)-1 from (select *, sum(case when x = 'off' then 1 else 0 end ) over(order by times) as a from
(select *, lag(status,1, 'on') over() as x from login_details)i)i
group by a having count(1) > 1

[@sammail96]

Good solution. Thanks. Oracle sql query for this : select min(times) as log_on, max(times) as log_off, count(a)-1 as duration
from
(
select i.*, sum(case when x = 'off' then 1 else 0 end ) over(order by times) as a
from
(
select ld.*, lag(status,1, 'on') over(order by times) as x from login_details ld
) i
) t
group by a
having count(1) > 1
order by log_on;

[@CebuProvince]

for ms sql:
select * from login_details
select min(times), max(times), count(a)-1 from (select *, sum(case when x = 'off' then 1 else 0 end ) over(order by times) as a from
(select *, lag(status,1, 'on') over( order by times) as x from login_details)i)i
group by a having count(1) > 1

[@sapnasaini851]

Great!! One thing I wanna add that if time is not continuous(exactly 1 minute difference) then count(a)-1 will not work for duration. We can achieve duration from "round(time_to_sec(timediff(max(times), min(times)))/60,0)"
Well, your solution is awesome!!

[@Damon-007]

with cte as(
select *,lag(status,1,'off') over(order by times ) lag
from login_details
),
cte2 as(select
datepart(minute,times) id1
,lead(datepart(minute,times), 1, 999) over(order by times) id2
, times, status,lag from cte
where (status='on' and lag='off')
)
select min(b.times)times ,count(*) login_min
from cte2 a
inner join cte b
on datepart(minute,b.times)
between a.id1 and a.id2-1
where b.status'off'
group by a.id1, a.id2;

[@Hsalz]

This series has been great. Sad we're nearing the end. Thanks for all the hard work you've put into this!

[@OmkarPatil-r9r]

Solved it using recursion instead of lead/lag.
with cte as (
select * , ROW_NUMBER() over(order by times) as rn from login_details
)
, recursive_Cte as (
select top 1 times as times , status , rn , 1 as 'login' , 1 as grouping_factor from cte where status ='on'
union all
select c.times ,
c.status ,
c.rn,
case when lower(r.status) = 'on' and lower(c.status) = 'off' then 0
when lower(r.status) = 'off' and lower(c.status) = 'on' then 1
END as 'login' ,
case when lower(r.status) = 'on' and lower(c.status) = 'off' then grouping_factor
when lower(r.status) = 'off' and lower(c.status) = 'on' then grouping_factor+1
ELSE grouping_factor
END
from cte c
inner join recursive_Cte r
on c.rn = r.rn + 1
)
select MAX(case when status ='on' then times END) as 'LOG_ON' ,
MAX(case when status ='off' then times END) as 'LOG_OFF' ,
DATEDIFF(MINUTE , MAX(case when status ='on' then times END) , MAX(case when status ='off' then times END) ) as Duration
from recursive_Cte
where login is not null
group by grouping_factor;
i think this solution can be used in any IDEs for SQL.

[@maheshrathi2608]

with cte as (select *,lag(status,1,'off') over(order by times) as rn from login_details)
select *,substring(ld,4,2)-substring(start_time,4,2) as count from (select start_time,lead(end_times,1,0) over(order by times) as ld from (select *,case when light = 1 then times else 0 end as start_time,case when light = 2 then times else 0 end as end_times from (select *,case when status = 'off' and rn = 'on' then 2 when status = "on" and rn = "off" then 1 else 0 end as light from cte) where light in (1,2))) where ld != 0 and start_time != 0

[@devrajpatidar5927]

Hello sir,
Nice Video and nice explaination
Here is my approach to this problem:--
with cte as (
select *,lag(status,1) over(order by times asc) as next_status from users_logins_details1
)
,cte1 as (
select *,
sum(case when status ='on' and next_status = 'off' then 1
else 0 end) over(order by times asc) as grp
from cte
)
select min(times) as login,max(times) as logout,datediff(MINUTE,min(times),max(times)) as duration
from cte1
where status 'off' or next_status 'off'
group by grp;

[@diptikar-s4f]

In Spark SQL
%sql
with T as (select times, status,
lag(status, 1 , 0) over (order by times) as P
from B),
J as
(select times ,
(case when T.status = 'on' then lead(times) over (order by times) else 0 end) as status
from T
where T.status P )
select
*, replace(status ,':','') - replace(times ,':' , '') as I
from J
where J.status '0'

[@sammail96]

Oracle sql query: with cte as(
select times, status, row_number() over (order by times) as rnk from (
select times, status, rk, first_value(rk) over (partition by flag order by flag) as rk1, count(*) over (partition by flag order by flag) as total from (
select times, status, rn, rank() over (order by times) as rk, (rn - rank() over (order by times)) as flag
from
(
select times, status, row_number() over (order by times) as rn
from login_details
)
where status = 'on'))
where rk = rk1
),
cte1 as (
select times, status, row_number() over (order by times) as rnk from (
select times, status, rk, first_value(rk) over (partition by flag order by flag) as rk1, count(*) over (partition by flag order by flag) as total from (
select times, status, rn, rank() over (order by times) as rk, (rn - rank() over (order by times)) as flag
from
(
select times, status, row_number() over (order by times) as rn
from login_details
)
where status = 'off'))
where rk = rk1
)
select TO_CHAR(c.times, 'HH24:MI:SS') as log_on, TO_CHAR(c1.times, 'HH24:MI:SS') as log_off, extract(minute from (c1.times - c.times)) as duration
from cte c join cte1 c1 on c.rnk = c1.rnk;

[@shivinmehta7368]

select log_on, ntime as log_off,extract(minute from ntime-log_on) as duration
from(
select grp,min(times) as log_on , max(times) as mt
from
(select *,
row_number() over (order by times) as rn,
sum(case when trim(status)='on' then 1 else 0 end) over (order by times) as csum,
row_number() over (order by times) - sum(case when trim(status)='on' then 1 else 0 end) over (order by times) as grp
from login_details
) x where trim(status)='on'
group by 1
) y left join (select times, lead(times) over(order by times) as ntime from login_details) b on y.mt=b.times

[@iamkiri_]

MySolution
with cte as (select times , rnk - row_number() over(order by times) as grpid from (
select *, row_number() over(order by times) as rnk from login_details ) Q
where status = 'on'),
cte2 as (
select grpid, cast(max(times) as time) as latestlogin,
cast(min(times) as time) as firstlogin from cte
group by grpid )
,cte3 as (
select * , lead(times) over(order by times) as logoff
from (
select l.times, firstlogin , latestlogin from login_details l
left join cte2 c
on l.times = c.latestlogin
order by l.times) Q )
select firstlogin as login, logoff ,
extract(minute from logoff) - extract(minute from firstlogin ) as totaltime
from cte3
where firstlogin is not null ;

[@mdmarufnawaz]

with cte as (select *, sum(case when status='on' and prev_status='off' or status='off' and prev_status='off' then 1 else 0 end) over (order by times)as grp_key
from(
select times, status, lag(status,1,status) over (order by times) as prev_status
from login_details) s
),
cte_final as (
select min(times) as login_time, max(times) as logoff_tim, datediff(minute, min(times), max(times)) as duration
from cte
group by grp_key)
select * from cte_final
where duration!=0

[@veeraraghavanr-sy3by]

Sir, how to practice these examples any idea?

[@karankumarrayat6282]

WITH t1 AS (
SELECT *,
CASE WHEN status = 'on' THEN 1 ELSE 0 END AS new
FROM login_details
),
t2 AS (
SELECT *,
LAG(new, 1, 0) OVER (ORDER BY times) AS new_n
FROM t1
),
t3 AS (
SELECT *,
new - new_n AS diff
FROM t2
WHERE new - new_n IN (1, -1)
),
t4 AS (
SELECT times,
status,
LEAD(times) OVER (ORDER BY times) AS newnn
FROM t3
)
SELECT times AS log_on,
newnn AS log_off,
DATEDIFF(MINUTE, times,newnn) AS duration
FROM t4
WHERE status = 'on'

[@NabeelKhan-um1zk]

select * from login_details;
create table log_inn as (with a as (select * , row_number() over(order by times) as ranks from login_details)
select times as log_in from a where ranks in (1,5,10,16) );
create table logg_offs as (with b as ( select * , row_number() over(order by times) as ranks from login_details)
select times as logg_off from b where ranks in (4,7,14,17) );
create table report as (with c as (select * , row_number() over() as ranks from log_inn) ,
d as (select * , row_number() over() as ranks from logg_offs)
select log_in,logg_off from c join d using(ranks));
select * from report ;
with a as (select * , timediff(logg_off,log_in) as duration from report)
select * , minute(duration) as duration from a;

[@essak3219]

Did it with a little addition of different users
create table input (
username varchar,
times timestamp,
status varchar);
insert into input
values
('user1','2024-05-01 10:00:00', 'on'),
('user2','2024-05-01 10:00:01', 'on'),
('user3','2024-05-01 10:00:03', 'on'),
('user1','2024-05-01 10:00:03', 'off'),
('user2','2024-05-01 10:00:05', 'off'),
('user1','2024-05-01 10:00:06', 'on'),
('user3','2024-05-01 10:00:07', 'off'),
('user2','2024-05-01 10:00:07', 'on'),
('user1','2024-05-01 10:00:09', 'off'),
('user1','2024-05-01 10:00:11', 'on'),
('user2','2024-05-01 10:00:18', 'off'),
('user2','2024-05-01 10:00:10', 'on'),
('user2','2024-05-01 10:00:12', 'off'),
('user1','2024-05-01 10:00:15', 'off');
with users_on as(
select username,times,status, row_number() over(partition by username,status order by times asc) as rn from input
where status = 'on')
, users_off as (
select username,times,status, row_number() over(partition by username,status order by times asc) as rn from input
where status = 'off'
)
select a.username, a.times as log_on, b.times as log_off, b.times - a.times as durtion
from users_on a inner join users_off b
on a.username = b.username
and a.rn = b.rn;

[@shaifahmad1227]

A shorter solution in MS SQL:
with cte as(
select times, status, lead(times) over(order by times) as lead_time from login_details),
cte2 as(
select times, dateadd(minute, -1*row_number() over(order by times),times) as active , status, row_number() over(order by times) as rn, lead_time
from cte
where status = 'on')
select min(times) as log_in, max(lead_time) as log_off, DATEDIFF(minute, min(times), max(lead_time)) as duration
from cte2
group by active;

[@karangupta_DE]

with cte as (
select times, status,
lag(status)over(order by times) as previous_status
from login_details
),
cte1 as (
select times, status, previous_status,
sum(case when status = 'on' and previous_status = 'off' then 1 else 0 end)over(order by times) as group_key
from cte
)select group_key, min(times) as login_time, max(times) as logoff_time,
count(case when previous_status = 'on' and status = 'off' then null else status end) as total
from (
select times, previous_status, status, group_key
from cte1
where true
and not(previous_status = 'off' and status = 'off')
)
group by group_key;

[@varunas9784]

fantastic series Thoufik!
here's me attempting on SQL server:
=============================================
with cte as (select *,
case when LAG(status, 1) over(order by times) = status then 0
when LAG(status, 1) over(order by times) is null then 0
else 1
end [flag]
from login_details),
grp as (select *, SUM(flag) over(order by times) grp_flag from cte)
select MIN(g1.times) [login time], MIN(g2.times) [logout time], DATEDIFF(MINUTE, MIN(g1.times), MIN(g2.times)) [Duration]
from grp g1
join grp g2 on g1.status g2.status and g1.grp_flag + 1 = g2.grp_flag
where g1.status ='on'
group by g1.grp_flag
============================================

[@akashjha7277]

can’t we solve this by self join? By creating 2 different cte of row_number of on and off separately. Then join off greater than on and rnk=1

[@ishanshubham8355]

MY attempt on MYSQl with cte as (
select *,
row_number() over(order by times) as rn,
row_number() over(partition by status order by times) as rn1,
row_number() over(order by times)- row_number() over(partition by status order by times) as diff
from login_details
order by times),
cte2 as (
select min(times) as times,status,diff
from cte
group by status,diff),cte3 as (
select *,lead(status) over(order by times) as ld,lead(times) over(order by times) as ld1
from cte2)
select times as log_on,
ld1 as log_off,
minute(timediff(ld1,times)) as duration
from cte3
where status ="on" and ld="off"

[@mcolsenet]

if you could provide contents about manipulating dates that would be fantastic. For example window function on 2nd quarter of the year or last day of the month or within a range of date/time etc

[@tonysun203]

Thank you for your brilliant and hard work. Really appreciate. 👍

[@luckyqueen7540]

Thank you

[@noufeerbk3543]

When will be the next sql full course ?

[@CebuProvince]

I hope soon

[@noufeerbk3543]

@@CebuProvince waiting

[@hariikrishnan]

if this was intermediate then i'm cooked

[@rajashekharreddy5794]

I have made my attempt in MS SQL
;with cte as (
select *
,isnull(case when lag(status) over (order by times)status then 1 end,0) [lag]
from login_details )
,cte2 as (
select *,1 [lag] from login_details
where (select Min(times) from login_details)=times
union all
select * from cte
where status in ('on','off') and [lag] = 1 )
, cte3 as (
select A.times [logintime],a.status [login]
,b.times [logofftime],b.status [logoff]
,ROW_NUMBER() over (partition by b.times order by b.times) [rn]
,count(*) over (partition by b.times order by b.times) [cnt]
from
(select *from cte2 where status = 'on') a
join
(select *from cte2 where status = 'off') b
on a.times