SQL Data Analysis Interview Question #22/100 | SQL Challenge | SQL Tutorials | Business Analyst |

Welcome to Day 22 of my 100 Days Challenge!
Join me as I walk through each problem step-by-step, providing detailed explanations and practical tips. Don't forget to like, subscribe, and hit the bell icon to stay updated with daily challenges!
Links:
GitHub Repository - Get the question & datasets
github.com/naji...
1:1 Mentorship - topmate.io/zer...
SQL Live Workshop - zeroanalyst.co...
Excel Live Workshop - zeroanalyst.co...
Best Data Analytics Course:
Data Analyst Courses by Meta - imp.i384100.ne...
Data Analytics Foundation by Google - imp.i384100.ne...
Microsoft Business Analyst Course - imp.i384100.ne...
Microsoft Power Bi End to End Course - imp.i384100.ne...
My Other KZbin Videos -
SQL 30 Days Road Map - [Free Learning Resources]
- • SQL For Beginners | Gr...
How to Install PostgreSQL in Windows/Mac - • Learn SQL for Data Ana...
How Import Data into PostgreSQL (PgAdmin 4) - • Effortless Data Import...
Group By Video - • SQL For Beginners | Gr...
#SQL #DataAnalysis #PortfolioProject #DataCleaning #BusinessAnalysis #DataAnalyst #SQLProject #EDA #SQLTutorial #GitHub #DataScience #CareerInData #LearnSQL #SQLforDataAnalyst #AspiringDataAnalyst #ResumeProject #EndToEndProject

[@vinothkumars7421]

Intersting one

[@anshusharaf2019]

My Approach! with cte as ( select user_id, login_date, lead(login_date) over(partition by user_id order by login_date) as next_Login_date from user_activity ), cte2 as ( select user_id, (julianday(next_Login_date) - julianday(login_date)) AS diff from cte ) SELECT user_id from cte2 GROUP by user_id, diff having COUNT('diff') >=2 ;

[@Savenature635]

with cte as (select *,row_number() over(partition by user_id order by login_date) as rn, day(login_date)-row_number() over(partition by user_id order by login_date) as grp from user_activity), cte_2 as ( select *,case when grp=0 then 'yes' else 'no' end as grp_flag from cte) select user_id from cte_2 where grp_flag='yes' group by user_id,grp_flag having count(1)>=3;

[@avinashjadon4989]

with cte as ( select * , lag(login_date,1) over (partition by user_id order by login_date asc) as next_day, lead(login_date,1) over (partition by user_id order by login_date asc) as previous_day from user_activity), final as( select *, abs(datediff(login_date,next_day)) as next_daylogin, abs(datediff(login_date,previous_day)) as previous_daylogin from cte) select user_id from final where (next_daylogin = null or next_daylogin=1) and previous_daylogin = 1 group by user_id;

[@chinmay.dunakhe]

My Solution : with cte1 as( select user_id, login_date, lag(login_date, 1, 0) over(partition by user_id order by login_date) as prev_date, datediff(login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date)) as diff from user_activity), cte2 as ( select *, #case when diff

[@tushardey7179]

SELECT * FROM ccdb.user_activity ua1 left join ccdb.user_activity ua2 on ua1.user_id = ua2.user_id and ua1.login_date = ua2.login_date - interval 1 day left join ccdb.user_activity ua3 on ua2.user_id = ua3.user_id and ua2.login_date = ua3.login_date - interval 1 day where ua3.user_id is not null

[@HARSHRAJ-gp6ve]

with cte as( select user_activity.*,LEAD(login_date)OVER(PARTITION BY user_id ORDER BY login_date) AS x1, LEAD(login_date,2)OVER(PARTITION BY user_id ORDER BY login_date) AS x2 FROM user_activity ),cte1 as( select cte.*,DATEDIFF(x1,login_date) as x3,DATEDIFF(x2,x1) as x4 FROM cte ) select DISTINCT(user_id) FROM cte1 where x3=1 and x4=1;

[@rockstarreporter]

This answer is also doing the same SELECT e1.user_id FROM user_activity e1 JOIN user_activity e2 ON e1.user_id = e2.user_id AND e2.login_date = e1.login_date + INTERVAL '1 DAY' JOIN user_activity e3 ON e1.user_id = e3.user_id AND e3.login_date = e1.login_date + INTERVAL '2 DAY' GROUP BY e1.user_id;