158 ) query to find out all sessions ,durations of a user in a portal

 

use temp_db;

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

-- 1. DDL: Create the user_sessions table

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

CREATE TABLE user_sessions (

    session_id INT PRIMARY KEY,

    user_id INT NOT NULL,

    session_timestamp TIMESTAMP NOT NULL,

    session_duration INT NOT NULL -- duration in minutes

);

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

-- 2. DML: Insert 10 sample rows

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

INSERT INTO user_sessions (session_id, user_id, session_timestamp, session_duration) VALUES

(1, 101, '2026-08-01 09:30:00', 30),

(2, 101, '2026-08-01 14:15:00', 45), -- User 101 has two sessions on Aug 1

(3, 101, '2026-08-02 10:00:00', 60),

(4, 102, '2026-08-02 11:30:00', 20),

(5, 101, '2026-08-03 16:45:00', 15),

(6, 103, '2026-08-04 08:00:00', 90),

(7, 102, '2026-08-05 13:20:00', 40),

(8, 101, '2026-08-06 19:10:00', 50),

(9, 103, '2026-08-07 21:00:00', 30),

(10, 101, '2026-08-08 08:30:00', 25);

INSERT INTO user_sessions (session_id, user_id, session_timestamp, session_duration) VALUES

-- User 101 sessions on Aug 1 and Aug 2

(1,  101, '2026-08-01 08:15:00', 25),

(2,  101, '2026-08-01 11:30:00', 40),

(3,  101, '2026-08-01 14:00:00', 15),

(4,  101, '2026-08-01 17:45:00', 60),

(5,  101, '2026-08-01 21:10:00', 30),

(6,  101, '2026-08-02 09:00:00', 45),

(7,  101, '2026-08-02 12:20:00', 20),

(8,  101, '2026-08-02 15:10:00', 35),

(9,  101, '2026-08-02 18:30:00', 50),

(10, 101, '2026-08-02 22:00:00', 10),

-- User 102 sessions on Aug 1 and Aug 2

(11, 102, '2026-08-01 07:30:00', 15),

(12, 102, '2026-08-01 10:00:00', 30),

(13, 102, '2026-08-01 13:15:00', 45),

(14, 102, '2026-08-01 16:40:00', 25),

(15, 102, '2026-08-01 19:20:00', 60),

(16, 102, '2026-08-02 08:45:00', 20),

(17, 102, '2026-08-02 11:10:00', 40),

(18, 102, '2026-08-02 14:30:00', 35),

(19, 102, '2026-08-02 17:00:00', 15),

(20, 102, '2026-08-02 20:15:00', 50);

-- truncate user_sessions;

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

-- 3. SELECT STATEMENT: Rolling 7-day sum

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

WITH daily_user_activity AS (

    SELECT 

        user_id,

        CAST(session_timestamp AS DATE) AS session_date,

        COUNT(*) AS total_sessions,

        SUM(session_duration) AS total_duration

    FROM 

        user_sessions

    GROUP BY 

        user_id, 

        CAST(session_timestamp AS DATE)

)

SELECT 

    user_id,

    session_date,

    total_sessions,

    total_duration

FROM 

    daily_user_activity

ORDER BY 

    user_id, 

    session_date;