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 !

[@Anishcko13]

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

[@fathimafarahna2633]

Awesome .. I find LearnSQL platform very helpful & useful

[@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;

[@ehsanshakeri620]

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

[@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

[@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

[@MdZeeshan-m9u]

Thank You so much sir

[@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;

[@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

[@Chenga-bt6tx]

It's very useful

[@tonysun203]

Brilliant video 👍

[@CebuProvince]

i agree 🏁

[@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

[@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

[@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

[@anudeepreddy5559]

🔥

[@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.

[@apavanipraneetha7180]

Please explain these solutions without using CTE

[@DEwithDhairy]

PySpark Version of this problem : kzbin.info/www/bejne/onWac3aogtmrras

[@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 ;

[@DEwithDhairy]

PySpark 30 days challenge for these problems : Solving each question step by step : kzbin.info/aero/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0

[@rohitsharma-mg7hd]

there are only 8 videos in this not 30

[@DEwithDhairy]

@@rohitsharma-mg7hd Videos will come in future !

[@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;

[@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;

[@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

[@pothinenivenkat]

Simplified

[@anantnegi5448]

I am unable to open the dataset links

[@mkgeidam]

Thanks for the video and dataset, its helpful @techTFQ

[@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

[@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;

[@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

[@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

[@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;