Solving A Hard SQL Problem | SQL ON OFF Problem | Magic of SQL

In this video we will see the magic of SQL. You will realize how adorable and beautiful SQL is. Fall in love with SQL with this video.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

script:
create table event_status
(
event_time varchar(10),
status varchar(10)
);
insert into event_status
values
('10:01','on'),('10:02','on'),('10:03','on'),('10:04','off'),('10:07','on'),('10:08','on'),('10:09','off')
,('10:11','on'),('10:12','off');

#sql #dataengineer #magic

[ankitbansal]

Please like the video if you learnt something new 🙂

[AshutoshVerma-dz]

another approach-
with temp as(
select *,
rank() over( order by event_time) as rnk,
right(event_time, 1)-rank() over( order by event_time) as flag from event_status order by event_time)

select min(event_time) as login, max(event_time) as logout, count(status)-1 as cnt from temp
group by flag

[mantisbrains]

select min(event_time) as log_in, max(event_time) as log_off, count(diff)-1 as on_count from
(select *, cast(substring as int) - row_number as diff from
(select *, substring(event_time, 4, 2), row_number() over () from
(select * from event_status )a)b)v
group by diff order by log_in, log_off;

Thank you, Ankit!

[mdaatifraza]

maintaining my consistency 😊

My approach for this problem

with cte as (select
*,
rank() over(order by event_time) as rnk,
cast(DATEADD(MINUTE, -1*rank() over(order by event_time), event_time)as time) as _time
from event_status)

select
min(event_time) as log_in,
max(event_time) as log_out,
count(rnk)-1 as cnt
from cte
group by _time

[adwaitbangale]

my approach-
with cte as(
select *, sum(case when status='on' then 0 else 1 end) over(order by event_time desc) grp_key
from event_status )
select min(event_time) login, max(event_time) logout, count(*)-1 count
from cte
group by grp_key
order by grp_key desc

[rawat]

with cte as(
select *, sum(new_status) over(order by event_time) as running_sum from(
select *,
case when status = 'on' and lag(status) over(order by event_time) = 'off' then 1 else 0 end as new_status
from event_status)temp)
select min(event_time) as login, max(event_time) as logout, count(running_sum)-1 as cnt
from cte
group by running_sum;

[apurvasaraf]

with cte as ( select *, (case when status ='on' and
lag(status) over(order by event_time)='off'
then 1 else 0 end) as c
from event_status
)
, cte1 as (select *, sum(c) over(order by event_time)as s from cte )
select min(event_time), max(event_time), count(*) from cte1
group by s

[explorer_baba]

After seeing your explanation, even harder problem seems to be easier . Keep going man .🥊🥊🥊

[chwaleedsial]

Nice. This is an excellent example of Gaps & Island type problems. I have seen another way to solve where they develop a general counter & than a sequence wise counter & than take a difference to id a specific sequence. This is nice & more straightforward. Usage of case within sum makes it more simpler to grasp.



Summary as I get it:
Step 1: Get previous status using lag
Step 2: In order to create an id for the sequence of on/off, use case within SUM to create a group key. This is basically continuously checking if the order is changing from previous value.
Step 3: Use the group key to get anything.

[vijaygupta]

with cte as
(
Select *,
cast(SUBSTRING(event_time, 4, 2) as int) - row_number()over(order by event_time) as grp
from event_status
)
Select min(event_time) as login, max(event_time) as logout
, count(case when status='on' then 1 else null end) as cnt
from cte
group by grp

[kanchankumar]

Brilliantly put together.. SQL baba rocks !!

[varunas]

Good one as always!

My attempt on SQL server:


with flagged_grp as (select *,
when LAG(status, 1, status) over(order by event_time) = status then 0 else 1 end [flag]
event_status),

status_grp as (select *,
over(order by event_time) [grp]
from flagged_grp),

sorted_grp as (select *, ROW_NUMBER() over(partition by [grp] order by event_time) [row num],
over(partition by grp) [count]
from status_grp)

select s1.event_time [login], s2.event_time [logout], s1.[count]
from sorted_grp s1
join sorted_grp s2 on s1.[row num] = s2.[row num] and s1.grp + 1 = s2.grp and s1.status = 'on' and s2.status = 'off'

[vivekkumarsingh]

Hi Ankit, great video. I did the grouping using right(time, 2)-row_number over( order by right(time, 2))since it was varchar and there is no continuous time. It will fail when there is a continuous 10.05 ON event after an 10.04 off because the groups are a difference of continuius row numbers that will still be continuous. So your approach makes more sense in this case.

[rahulmehla]

my solution:
with cte as(
select *, minute(event_time)-row_number() over() as rn from event_status)
select min(event_time) as login, max(event_time) as logout, count(1)-1 as cnt
from cte group by rn;

[arthurmorgan]

Hi Ankit,
here's my solution

with cte as
(
SELECT *, ROW_NUMBER() over(order by event_time) as timing FROM EVENT_STATUS
)
, cteone as
(
select event_time, format(dateadd(minute, -timing, event_time), 'hh:mm') as va, status from cte
)
select min(event_time) as login, max(event_time) as logout, count(va) as cnt from cteone
group by va

[RaviKanth-fxpt]

with cte as(
select right(event_time, 2) - ROW_NUMBER() over(order by event_time) + 1 as parts
, event_time, status
from event_status)
, cte2 as(
select parts, MIN(event_time) as min_time, MAX(event_time) as max_time, count(status) - 1 as on_count
from cte
group by parts)
select min_time as login_time, max_time as logout_time, on_count as cnt
from cte2

[gowrisankarsabbavarapu]

with cte as(
select event_time, status, sum(case when status='on' then 0 else 1 end) over (order by event_time) as grp
from event_status)
select min(event_time) as in_time, max(event_time) as out_time, count(1)-1 as cnt
from cte
group by (case when status='on' then grp else grp-1 end)

[dhemanth]

Hi bansal i used to struggle to cretae group keys but idea of running total is purely genius thank you

[yeknathmerwade]

with cte as(
select *, (case when previous_status='on' then 1 else 0 end) as checks,
sum(case when status='on' and previous_status='off' then 1 else 0 end) over (order by event_time) as group_key from
(select *, LAG(status, 1) over (order by event_time) as previous_status from event_status) a
)
select min(event_time) as log_in_time, max(event_time) as log_out_time, sum(checks) as on_count from cte
group by group_key

[karangupta_DE]

I was able to create the first part but got stuck while creating group keys, thank you so much for explaining the same, learned a new concept today. Thank you :)