Compare Weekly User Activity Trends | Dataford Interview Questions

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.

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

Your question is Compare Weekly User Activity Trends. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem