Compare Weekly User Activity Trends

Medium

SQL & Data Manipulation

Asked at 12 companies12Window FunctionsLag/LeadDate Functions

Also asked at

Problem

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	minutes_wow_change
101	Ava Patel	2024-01-15	2	80	2	75	5
102	Ben Carter	2024-01-08	2	50	2	25	25
103	Chloe Kim	2024-01-15	2	35	2	40	-5

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user signed up
plan_type	VARCHAR(20)	Subscription plan type

watch_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_start	TIMESTAMP	Timestamp when the session started
minutes_watched	INT	Minutes watched during the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

Example 1

Input

{"users":[["101","Ava Patel","2024-01-02","Premium"],["102","Ben Carter","2024-01-05","Basic"],["103","Chloe Kim","2024-01-10","Premium"],["104","Diego Lopez","2024-01-12","Basic"],["105","Emma Reed","2024-01-15",null],["106","Farah Ali","2024-01-18","Premium"],["107","Gavin Brooks","2024-01-20","Basic"],["108","Hana Suzuki","2024-01-22","Premium"],["109","Zane Cooper","2024-01-22","Annual"],["110","Zane Cooper","2024-01-22","Promo"],["111","Farah Ali","2024-01-03","Student"],["112","Willa Scott

Output

[["101","Ava Patel","2024-01-15","2","80","2","75","0","5"],["102","Ben Carter","2024-01-01","4","-4","1","50","3","-54"],["102","Ben Carter","2024-01-08","4","80","4","-4","0","84"],["102","Ben Carter","2024-01-15","3","45","4","80","-1","-35"],["102","Ben Carter","2024-01-22","1","-10","3","45","-2","-55"],["103","Chloe Kim","2024-01-01","1","3","1","63","0","-60"],["103","Chloe Kim","2024-01-08","3","102","1","3","2","99"],["103","Chloe Kim","2024-01-15","2","35","3","102","-1","-67"],["103",

Problem

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	minutes_wow_change
101	Ava Patel	2024-01-15	2	80	2	75	5
102	Ben Carter	2024-01-08	2	50	2	25	25
103	Chloe Kim	2024-01-15	2	35	2	40	-5

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user signed up
plan_type	VARCHAR(20)	Subscription plan type

watch_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_start	TIMESTAMP	Timestamp when the session started
minutes_watched	INT	Minutes watched during the session
device_type	VARCHAR(20)	Device used for the session

Sample Data

Example 1

Input

{"users":[["101","Ava Patel","2024-01-02","Premium"],["102","Ben Carter","2024-01-05","Basic"],["103","Chloe Kim","2024-01-10","Premium"],["104","Diego Lopez","2024-01-12","Basic"],["105","Emma Reed","2024-01-15",null],["106","Farah Ali","2024-01-18","Premium"],["107","Gavin Brooks","2024-01-20","Basic"],["108","Hana Suzuki","2024-01-22","Premium"],["109","Zane Cooper","2024-01-22","Annual"],["110","Zane Cooper","2024-01-22","Promo"],["111","Farah Ali","2024-01-03","Student"],["112","Willa Scott

Output

[["101","Ava Patel","2024-01-15","2","80","2","75","0","5"],["102","Ben Carter","2024-01-01","4","-4","1","50","3","-54"],["102","Ben Carter","2024-01-08","4","80","4","-4","0","84"],["102","Ben Carter","2024-01-15","3","45","4","80","-1","-35"],["102","Ben Carter","2024-01-22","1","-10","3","45","-2","-55"],["103","Chloe Kim","2024-01-01","1","3","1","63","0","-60"],["103","Chloe Kim","2024-01-08","3","102","1","3","2","99"],["103","Chloe Kim","2024-01-15","2","35","3","102","-1","-67"],["103",

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

7-Day Rolling Active UsersMedium

Monthly User Engagement Trend AnalysisMedium

Weekly Active Purchaser Growth RateMedium

Next question

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	minutes_wow_change
101	Ava Patel	2024-01-15	2	80	2	75	5
102	Ben Carter	2024-01-08	2	50	2	25	25
103	Chloe Kim	2024-01-15	2	35	2	40	-5