Task
StreamWave wants to measure how each user’s activity changes from one week to the next. Write a SQL query to compare weekly behavior using window functions.
Requirements
- For each active user and calendar week, calculate:
- total sessions
- total minutes watched
- Compare each week to the user’s previous active week.
- Return the week-over-week change in both sessions and minutes watched.
- Only include rows where a previous week exists.
- Order the final output by
user_id and week_start.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Primary key for each user |
| user_name | VARCHAR(100) | User name |
| signup_date | DATE | Date the user signed up |
| plan_type | VARCHAR(20) | Subscription plan |
watch_sessions
| column | type | description |
|---|
| session_id | INT | Primary key for each session |
| user_id | INT | User who created the session |
| session_start | TIMESTAMP | Session start time |
| minutes_watched | INT | Minutes watched in the session |
| device_type | VARCHAR(20) | Device used for the session |
Sample Data
users
| user_id | user_name | signup_date | plan_type |
|---|
| 101 | Ava Patel | 2024-01-02 | Premium |
| 102 | Ben Carter | 2024-01-05 | Basic |
| 103 | Chloe Kim | 2024-01-10 | Premium |
watch_sessions
| session_id | user_id | session_start | minutes_watched | device_type |
|---|
| 1007 | 101 | 2024-01-15 19:30:00 | 20 | Mobile |
| 1001 | 101 | 2024-01-02 09:00:00 | 30 | TV |
| 1002 | 101 | 2024-01-03 20:00:00 | 45 | Mobile |
| 1004 | 102 | 2024-01-04 18:00:00 | 25 | Web |
| 1005 | 102 | 2024-01-10 21:00:00 | 50 | TV |
| 1009 | 103 | 2024-01-17 12:00:00 | NULL | Mobile |
Expected Output
| user_id | user_name | week_start | total_sessions | total_minutes_watched | previous_week_sessions | previous_week_minutes_watched | sessions_wow_change | minutes_wow_change |
|---|
| 101 | Ava Patel | 2024-01-15 | 2 | 80 | 2 | 75 | 0 | 5 |
| 102 | Ben Carter | 2024-01-08 | 2 | 50 | 2 | 25 | 0 | 25 |
| 103 | Chloe Kim | 2024-01-15 | 2 | 35 | 2 | 40 | 0 | -5 |
