Interview Guides

Compare Weekly User Activity Trends | Dataford Interview Questions

Asked 1mo ago|

eBay

Compare Weekly User Activity Trends

MediumWindow FunctionsLag/LeadDate Functions

Problem

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

Problem

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

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

Measure Week-Over-Week Metric ChangesMedium

Next question

Asked 1mo ago|

eBay

Compare Weekly User Activity Trends

MediumWindow FunctionsLag/LeadDate Functions

Problem

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

Problem

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

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

Measure Week-Over-Week Metric ChangesMedium

Next question