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;
