Consecutive User Logins - SQL Interview Query 17 | SQL Problem Level "MEDIUM"

[@Travellerkida]

It is very useful and informative, as we proceed day by day, I learn new logic to solve new problems. How you achieve this much competency in sql. I am amazed !

[@DEwithDhairy]

Solutioin Using row_number()
with cte as (
select * , row_number() over(partition by user_id order by login_date) as rn
from (
select user_id , login_date
from user_login
group by user_id , login_date
) as A
order by user_id , login_date
) , cte2 as (
select * , (extract('day' from login_date) - rn) as group_
from cte
)
--select * from cte2
select user_id , min(login_date) as start_date , max(login_date) as end_date , count(1) as consicutive_days
from cte2
group by user_id , group_
having count(1) > 4;

[@sureshraina321]

My solution , not optimized but still I tried with my knowledge
with cte as
(
select user_id,
login_date,
case
when lag(substr(login_date,1,2)) over(partition by user_id order by login_date) is null then 1
else
substr(login_date,1,2) - lag(substr(login_date,1,2)) over(partition by user_id order by login_date)
end as day_diff_lag,
case
when lead(substr(login_date,1,2)) over(partition by user_id order by login_date) is null then 1
else
lead(substr(login_date,1,2)) over(partition by user_id order by login_date) - substr(login_date,1,2)
end as day_diff_lead
from user_login
) ,
cte2 as (
select user_id,
login_date,
dense_rank() over(partition by user_id order by login_Date) - substr(login_date,1,2) as row_num
from cte
where day_diff_lag = 1 or day_diff_lead = 1
)
select user_id,min(login_date) as first_login,max(login_date) as last_login
from cte2
group by user_id,row_num
having count(distinct login_date) >=5
order by user_id

[@blse2000]

SQL SERVER SOLUTION:
GO
WITH CTE
AS
(
SELECT distinct USER_ID,login_date
,DENSE_RANK() over(partition by user_id order by login_date) as rn
,DAY(LOGIN_DATE) - DENSE_RANK() over(partition by user_id order by login_date) as diff
FROM user_login
),
CTE2
AS
(
SELECT user_id as USER_ID,MIN(login_date) as START_DATE,MAX(login_date) as END_DATE,COUNT(DIFF) as CONSECUTIVE_DAYS
FROM CTE
GROUP BY user_id,DIFF
HAVING COUNT(DIFF)>=5
)
SELECT * FROM CTE2 ORDER BY USER_ID

[@karankumarrayat6282]

with t1 as
(
SELECT distinct *
FROM user_login
),
t2 as (
selecT *,
DAY(login_date) as days,
row_number() over (partition by user_id order by user_id,login_date)as rn,
abs(DAY(login_date) - row_number() over (partition by user_id order by user_id,login_date)) as diff
from t1
)
select t2.user_id, min(login_date) Start_date, max(login_date) as end_date, count(*) as consecutive_days
from t2
group by t2.user_id, t2.diff
having count(*)>= 5
order by user_id

[@Anishcko13]

Great Initiative, 👍
Plz do a playlist on Power BI as well

[@brownwolf05]

----simple and optimised mysql solution
with cte_segment as (
select distinct user_id,login_date, date_sub(login_date,interval DENSE_RANK() over(PARTITION BY user_id order by login_date) day) as segment from user_login
)
select user_id, min(login_date) as start_date, max(login_date) as end_date, datediff(max(login_date),min(login_date))+1 as consecutive_days
from cte_segment group by user_id,segment having consecutive_days > 4;

[@sapnasaini851]

with cte1 as
(select *, date_sub(login_date, interval dense_rank() over(partition by user_id order by login_date) day) pre_day
from user_login),
cte2 as (
select user_id,pre_day, count(1) consecutive_days,
min(login_date) start_date, max(login_date) end_date
from cte1
group by user_id, pre_day)
select user_id, start_date, end_date, datediff(end_date,start_date)+1 consecutive_days
from cte2
where datediff(end_date,start_date) >=4
;

[@ehsanshakeri620]

thank you, that was really interesting❤... i solved it

[@sanjeetsignh]

with cte as
(
select *
, datepart(day, login_date) - dense_rank() over wnd as frame
from user_login
window wnd as (partition by user_id order by login_date)
)
select user_id
, min(login_date) as start_date
, max(login_date) as end_date
, count(distinct login_date) as consecutive_days
from cte
group by user_id, frame
having count(distinct login_date) >= 5
order by 1;

[@florincopaci6821]

My approach in Sql Server
with difference_days as(
select *, datediff(day, dense_rank()over(partition by user_id order by login_date),login_date)as diff
from user_login
), consecutive_days as (
select *, sum(count (distinct login_date))over(partition by user_id, diff)as consecutive_days
from difference_days
group by user_id, login_date, diff
)
select user_id, min(login_date)as start_date, max(login_date)as end_date, consecutive_days
from consecutive_days
group by user_id, consecutive_days
having max(consecutive_days)>4
order by 1
Hope it helps

[@ThePinanknagda]

hey in the outer query, you can use count(distinct login_date) and get the count of consecutive days ? pls correct me if i am wrong.. love your content

[@pothinenivenkat]

Simplified

[@DEwithDhairy]

PySpark Version of this problem :
th-cam.com/video/lD7BCrMtxwI/w-d-xo.html

[@camomile4085]

WITH cte AS
(SELECT *, login_date - (DENSE_RANK () OVER (ORDER BY user_id, login_date))::INT AS date_grp
FROM (SELECT DISTINCT (login_date), user_id FROM input_17)
)
SELECT user_id, MIN(login_date) AS start_date, MAX (login_date) AS end_date,
COUNT (*) AS consecutive_days
FROM cte
GROUP BY user_id, date_grp
HAVING COUNT (*) > 4

[@PriyaKaushal-v5r]

can you please explain how does count(1) work?

[@NischitSapkota]

It is just a counter , you could've had any value and it would've worked just fine.

[@DEwithDhairy]

PySpark 30 days challenge for these problems :
Solving each question step by step :
th-cam.com/play/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0.html

[@rohitsharma-mg7hd]

there are only 8 videos in this not 30

[@DEwithDhairy]

@@rohitsharma-mg7hd Videos will come in future !

[@anantnegi5448]

I am unable to open the dataset links

[@sahilummat8555]

;with cte as (
select distinct *
from [covid_cases_online2]
),cte2 as (
select *,
ROW_NUMBER()over(partition by user_id order by user_id,login_date ) as rn
from cte )
,cte3 as (
select *,
DATEADD(day,-rn ,login_date) as final_date,
count(1)over(partition by user_id,DATEADD(day,-rn ,login_date)) as cum_cnt
from cte2)
select user_id,MIN(login_date) as start_date
,max(login_date) as end_date,MAX(cum_cnt) as consecutive_days
from cte3 where cum_cnt>=5
group by user_id,final_date
order by user_id,final_date

[@subodhthore6454]

with cte_2st as (
select user_id,login_date,row_number() over(partition by user_id,login_date order by user_id) as rn_s from user_login
),
cte as (
select user_id , login_date, row_number() over(partition by user_id order by login_date )
as rn from cte_2st
where rn_s = 1)

,
cte1 as (
select * , login_date - rn::integer as dt from cte)
select user_id,dt, count(*), min(login_date) as start, max(login_date) as end from cte1
group by user_id,dt
having count(*)>=5;

[@Satish_____Sharma]

solved using MYSQL
with cte as (select distinct * from user_login)
select user_id as USER_ID,min(login_date) as START_DATE,max(login_date) as END_DATE,count(*) as CONSECUTIVE_DAYS
from (SELECT user_id, login_date,cast(extract(day from login_date) as decimal)-rank() over (partition by user_id order by login_date) as rn
FROM cte) a group by rn,user_id having count(login_date)>=5 order by USER_ID;

[@AdityaKumar-qi9ed]

anyone with mysql ? im not gettin it

[@deepakbehara198]

my solution using dense_rank
with cte as(
select *,dense_rank() over(order by user_id,login_date) - extract(day from login_date) as group_no from user_login
)
select user_id,min(login_date) as start_date,max(login_date) as end_date,max(login_date)-min(login_date)+1 as conseuctive_days
from cte
group by user_id,group_no
having max(login_date)-min(login_date)+1 >= 5
order by 1,2

[@saiswaroop3570]

Hi toufiq you are really a great job to deliver top class content for us , I hope you find enough to take rest for urself

[@sammail96]

Is there any approach to solve this problem? (without use of dense_rank(), rank() or row_number())

[@sammail96]

Oracle sql: with user_login1 as (
select distinct user_id,login_date from user_login
),
consecutive_login (user_id, login_date,consecutive_start) AS (
SELECT user_id, login_date, login_date AS consecutive_start
FROM user_login1
WHERE NOT EXISTS (
SELECT 1
FROM user_login1 prev
WHERE prev.user_id = user_login1.user_id
AND prev.login_date = user_login1.login_date - 1
)
UNION ALL
SELECT ul.user_id, ul.login_date,
CASE
WHEN ul.login_date = cl.login_date + 1 THEN cl.consecutive_start
ELSE ul.login_date
END AS consecutive_start
FROM user_login1 ul
JOIN consecutive_login cl ON ul.user_id = cl.user_id AND ul.login_date = cl.login_date + 1
)
SELECT
user_id,
MIN(consecutive_start) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM consecutive_login
GROUP BY user_id, consecutive_start
HAVING COUNT(*) >= 5
ORDER BY user_id, start_date;

[@fathimafarahna2633]

Awesome .. I find LearnSQL platform very helpful & useful

[@akshaysalve195]

with cte as (
Select user_id,login_date,
lag(login_date) over(partition by user_id order by login_date) as previous_date
from user_login
)
Select cte.user_id,
count(*) as cons_days from cte
where cte.login_date = date_add(cte.previous_date, Interval 1 day)
group by user_id
having count(*) >= 5;

[@apavanipraneetha7180]

Please explain these solutions without using CTE

[@MdZeeshan-m9u]

Thank You so much sir

[@Chenga-bt6tx]

It's very useful

[@tonysun203]

Brilliant video 👍

[@CebuProvince]

i agree 🏁

[@mkgeidam]

Thanks for the video and dataset, its helpful @techTFQ

[@sabesanj5509]

WITH cte AS (
SELECT user_id,
login_date,
login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)AS grp
FROM user_login
)
WITH cte1 AS (
SELECT
user_id,
MIN(login_date) AS START_DATE,
MAX(login_date) AS END_DATE,
COUNT(*) AS consecutive_days
FROM cte
HAVING COUNT(*) >= 5
)
SELECT user_id,
start_date,
end_date
consecutive_days
FROM cte1
ORDER BY user_id, start_date;