158 )Find users session durations

 158 )Find users session durations 

-- ==========================================

use temp_db;

 use temp_db;

 -- ==========================================

 

 -- 1. Create Table

CREATE TABLE user_login (

    session_id INT AUTO_INCREMENT PRIMARY KEY,

    userid VARCHAR(50) NOT NULL,

    logintime DATETIME NOT NULL,

    logout_time DATETIME NOT NULL

);

-- ==========================================

-- 2. Insert Data

INSERT INTO user_login (userid, logintime, logout_time) VALUES

('user_001', '2026-08-10 08:30:00', '2026-08-10 09:15:00'),

('user_001', '2026-08-12 10:00:00', '2026-08-12 11:30:00'),

('user_002', '2026-08-11 09:00:00', '2026-08-11 09:45:00'),

('user_002', '2026-08-13 13:00:00', '2026-08-13 14:30:00'),

('user_003', '2026-08-09 14:10:00', '2026-08-09 15:00:00'),

('user_003', '2026-08-14 11:15:00', '2026-08-14 12:00:00'),

('user_001', '2026-08-14 16:20:00', '2026-08-14 17:50:00'),

('user_004', '2026-08-15 08:00:00', '2026-08-15 09:30:00'),

('user_004', '2026-08-15 10:00:00', '2026-08-15 10:45:00'),

('user_002', '2026-08-15 11:00:00', '2026-08-15 12:15:00');

--- ==========================================

- 3. Run Query to Find Average Session Durations in the Last 7 Days

-- ==========================================

 SELECT 

    userid,

    SEC_TO_TIME(

                AVG(    

                    TIME_TO_SEC(

                            TIMEDIFF(

                                    logout_time, logintime)))) AS avg_session_duration,

    AVG(TIMESTAMPDIFF(MINUTE, logintime, logout_time)) AS avg_duration_in_minutes

FROM user_login

WHERE logintime >= NOW() - INTERVAL 7 DAY

GROUP BY userid;

-- ==========================================

# userid, avg_session_duration, avg_duration_in_minutes

'user_001', '01:15:00.0000', '75.0000'

'user_002', '01:10:00.0000', '70.0000'

'user_003', '00:47:30.0000', '47.5000'

'user_004', '01:07:30.0000', '67.5000'