𝗖𝗮𝗹𝗰𝘂𝗹𝗮𝘁𝗲 𝗧𝗼𝘁𝗮𝗹 𝗗𝗮𝗶𝗹𝘆 𝗨𝘀𝗲𝗿 𝗔𝗰𝘁𝗶𝘃𝗶𝘁𝘆 𝗧𝗶𝗺𝗲 𝗶𝗻 𝗠𝘆𝗦𝗤𝗟 8.0 | 𝗔𝗺𝗮𝘇𝗼𝗻 (𝗛𝗮𝗿𝗱 𝗟𝗲𝘃𝗲𝗹) #𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻

In this video, we solve a real-world SQL problem that comes up constantly in product analytics and backend development — how to accurately calculate the total hours a user spends on your platform each day using raw login/logout event logs. Problem Statement Given the users' sessions logs, calculate the total hours each user spends on the platform each day. Note: The session starts when state=1 and ends when state=0. Schema: CREATE TABLE user_sessions ( user_id INT, session_time DATETIME, state INT -- 1 = session start, 0 = session end ); INSERT INTO user_sessions (user_id, session_time, state) VALUES (101, '2026-03-01 09:00:00', 1), (101, '2026-03-01 12:00:00', 0), (101, '2026-03-01 14:00:00', 1), (101, '2026-03-01 18:30:00', 0), (102, '2026-03-01 08:15:00', 1), (102, '2026-03-01 10:45:00', 0), (102, '2026-03-01 11:30:00', 1), (102, '2026-03-01 13:00:00', 0), (103, '2026-03-01 16:00:00', 1), (103, '2026-03-01 16:05:00', 0), (101, '2026-03-02 10:00:00', 1), (101, '2026-03-02 15:00:00', 0), (102, '2026-03-02 23:00:00', 1), (102, '2026-03-03 01:30:00', 0), (103, '2026-03-02 07:00:00', 1), (103, '2026-03-02 09:00:00', 0), (103, '2026-03-02 17:00:00', 1), (103, '2026-03-02 20:15:00', 0), (104, '2026-03-03 08:00:00', 1), (104, '2026-03-03 17:30:00', 0), (101, '2026-03-03 06:30:00', 1), (101, '2026-03-03 07:15:00', 0); In this video, we walk through the solution step by step and cover: ✅ Window Functions — LEAD() with PARTITION BY and ORDER BY ✅ Common Table Expressions (CTEs) ✅ Time Arithmetic with TIMESTAMPDIFF ✅ A hidden precision bug with TIMESTAMPDIFF(HOUR) — and the fix ✅ Filtering orphaned and invalid records ✅ Indexing for performance on large datasets What You'll Learn How to normalize a DATETIME column for daily grouping using DATE() How to pair related rows using LEAD() without any self-joins Why TIMESTAMPDIFF(HOUR) silently loses data — and how SECOND fixes it How to think through event-driven data problems step by step This question is perfect for: Data Analyst Interviews Data Engineer Interviews SQL Developer Interviews Backend Developer Interviews Analytics Engineering Interviews If you found this video helpful, please like, share, and subscribe for more SQL interview questions and step-by-step solutions. #SQL #WindowFunctions #MySQL #DataAnalyst #SQLInterviewQuestions #DataEngineering #LearnSQL #Database #BackendDevelopment #InterviewPrep #SQLTutorial #MySQL8