Interview Guides

7-Day Rolling Active Users | Dataford Interview Questions - Dataford - Ace your Interview

7-Day Rolling Active Users

Medium

SQL & Data Manipulation

Asked at 15 companies15Window FunctionsRunning TotalsDate Functions

Also asked at

Problem

Task

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Table Definitions

`user_events`

column	type	description
event_id	INT	Unique event record ID
user_id	INT	User who generated the event; can be NULL
event_name	VARCHAR(50)	Type of event
event_time	TIMESTAMP	Timestamp of the event

Sample Data

`user_events`

event_id	user_id	event_name	event_time
5	103	login	2024-03-03 08:00:00
1	101	login	2024-03-01 09:00:00
11	105	login	2024-03-05 12:00:00
3	101	purchase	2024-03-02 11:00:00
8	104	login	2024-03-04 10:30:00
14	NULL	app_open	2024-03-06 08:00:00
18	108	login	2024-03-08 14:00:00
20	109	login	2024-03-09 09:00:00

Expected Output

event_date	daily_active_users	rolling_7_day_avg
2024-03-01	2	2.0000
2024-03-02	2	2.0000
2024-03-03	2	2.0000
2024-03-04	3	2.2500
2024-03-05	2	2.2000
2024-03-06	1	2.0000
2024-03-07	2	2.0000
2024-03-08	2	2.0000
2024-03-09	2	2.0000

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred

Sample Data

Example 1

Input

{"user_events":[["5","103","login","2024-03-03 08:00:00"],["1","101","login","2024-03-01 09:00:00"],["11","105","login","2024-03-05 12:00:00"],["3","101","purchase","2024-03-02 11:00:00"],["8","104","login","2024-03-04 10:30:00"],["14",null,"app_open","2024-03-06 08:00:00"],["18","108","login","2024-03-08 14:00:00"],["20","109","login","2024-03-09 09:00:00"],["2","102","app_open","2024-03-01 10:00:00"],["4","102","login","2024-03-02 15:00:00"],["6","103","purchase","2024-03-03 09:00:00"],["7","1

Output

[["2024-02-29","1","1.0000"],["2024-03-01","2","1.5000"],["2024-03-02","2","1.6667"],["2024-03-03","2","1.7500"],["2024-03-04","2","1.8000"],["2024-03-05","2","1.8333"],["2024-03-06","1","1.7143"],["2024-03-07","2","1.8571"],["2024-03-08","2","1.8571"],["2024-03-09","2","1.8571"],["2024-03-11","2","1.8571"]]

Problem

Task

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Table Definitions

`user_events`

column	type	description
event_id	INT	Unique event record ID
user_id	INT	User who generated the event; can be NULL
event_name	VARCHAR(50)	Type of event
event_time	TIMESTAMP	Timestamp of the event

Sample Data

`user_events`

event_id	user_id	event_name	event_time
5	103	login	2024-03-03 08:00:00
1	101	login	2024-03-01 09:00:00
11	105	login	2024-03-05 12:00:00
3	101	purchase	2024-03-02 11:00:00
8	104	login	2024-03-04 10:30:00
14	NULL	app_open	2024-03-06 08:00:00
18	108	login	2024-03-08 14:00:00
20	109	login	2024-03-09 09:00:00

Expected Output

event_date	daily_active_users	rolling_7_day_avg
2024-03-01	2	2.0000
2024-03-02	2	2.0000
2024-03-03	2	2.0000
2024-03-04	3	2.2500
2024-03-05	2	2.2000
2024-03-06	1	2.0000
2024-03-07	2	2.0000
2024-03-08	2	2.0000
2024-03-09	2	2.0000

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred

Sample Data

Example 1

Input

{"user_events":[["5","103","login","2024-03-03 08:00:00"],["1","101","login","2024-03-01 09:00:00"],["11","105","login","2024-03-05 12:00:00"],["3","101","purchase","2024-03-02 11:00:00"],["8","104","login","2024-03-04 10:30:00"],["14",null,"app_open","2024-03-06 08:00:00"],["18","108","login","2024-03-08 14:00:00"],["20","109","login","2024-03-09 09:00:00"],["2","102","app_open","2024-03-01 10:00:00"],["4","102","login","2024-03-02 15:00:00"],["6","103","purchase","2024-03-03 09:00:00"],["7","1

Output

[["2024-02-29","1","1.0000"],["2024-03-01","2","1.5000"],["2024-03-02","2","1.6667"],["2024-03-03","2","1.7500"],["2024-03-04","2","1.8000"],["2024-03-05","2","1.8333"],["2024-03-06","1","1.7143"],["2024-03-07","2","1.8571"],["2024-03-08","2","1.8571"],["2024-03-09","2","1.8571"],["2024-03-11","2","1.8571"]]

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling DAU on FacebookHard

Daily User Engagement MetricsEasy

Compare Weekly User Activity TrendsMedium

Next question

7-Day Rolling Active Users

Medium

SQL & Data Manipulation

Asked at 15 companies15Window FunctionsRunning TotalsDate Functions

Also asked at

Problem

Task

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Table Definitions

`user_events`

column	type	description
event_id	INT	Unique event record ID
user_id	INT	User who generated the event; can be NULL
event_name	VARCHAR(50)	Type of event
event_time	TIMESTAMP	Timestamp of the event

Sample Data

`user_events`

event_id	user_id	event_name	event_time
5	103	login	2024-03-03 08:00:00
1	101	login	2024-03-01 09:00:00
11	105	login	2024-03-05 12:00:00
3	101	purchase	2024-03-02 11:00:00
8	104	login	2024-03-04 10:30:00
14	NULL	app_open	2024-03-06 08:00:00
18	108	login	2024-03-08 14:00:00
20	109	login	2024-03-09 09:00:00

Expected Output

event_date	daily_active_users	rolling_7_day_avg
2024-03-01	2	2.0000
2024-03-02	2	2.0000
2024-03-03	2	2.0000
2024-03-04	3	2.2500
2024-03-05	2	2.2000
2024-03-06	1	2.0000
2024-03-07	2	2.0000
2024-03-08	2	2.0000
2024-03-09	2	2.0000

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred

Sample Data

Example 1

Input

{"user_events":[["5","103","login","2024-03-03 08:00:00"],["1","101","login","2024-03-01 09:00:00"],["11","105","login","2024-03-05 12:00:00"],["3","101","purchase","2024-03-02 11:00:00"],["8","104","login","2024-03-04 10:30:00"],["14",null,"app_open","2024-03-06 08:00:00"],["18","108","login","2024-03-08 14:00:00"],["20","109","login","2024-03-09 09:00:00"],["2","102","app_open","2024-03-01 10:00:00"],["4","102","login","2024-03-02 15:00:00"],["6","103","purchase","2024-03-03 09:00:00"],["7","1

Output

[["2024-02-29","1","1.0000"],["2024-03-01","2","1.5000"],["2024-03-02","2","1.6667"],["2024-03-03","2","1.7500"],["2024-03-04","2","1.8000"],["2024-03-05","2","1.8333"],["2024-03-06","1","1.7143"],["2024-03-07","2","1.8571"],["2024-03-08","2","1.8571"],["2024-03-09","2","1.8571"],["2024-03-11","2","1.8571"]]

Problem

Task

StreamWave tracks user activity across its app and wants a daily engagement trend. Write a SQL query to calculate the 7-day rolling average of daily active users.

A user is considered active on a given date if they generated at least one event that day. Use the event log and return one row per calendar date present in the data.

Requirements

Count distinct active users per day from the user_events table.
Calculate the 7-day rolling average of daily active users, ordered by event date.
Include dates even when user_id is NULL; those rows should not count toward active users.
Return the columns event_date, daily_active_users, and rolling_7_day_avg.
Order the final result by event_date ascending.

Table Definitions

`user_events`

column	type	description
event_id	INT	Unique event record ID
user_id	INT	User who generated the event; can be NULL
event_name	VARCHAR(50)	Type of event
event_time	TIMESTAMP	Timestamp of the event

Sample Data

`user_events`

event_id	user_id	event_name	event_time
5	103	login	2024-03-03 08:00:00
1	101	login	2024-03-01 09:00:00
11	105	login	2024-03-05 12:00:00
3	101	purchase	2024-03-02 11:00:00
8	104	login	2024-03-04 10:30:00
14	NULL	app_open	2024-03-06 08:00:00
18	108	login	2024-03-08 14:00:00
20	109	login	2024-03-09 09:00:00

Expected Output

event_date	daily_active_users	rolling_7_day_avg
2024-03-01	2	2.0000
2024-03-02	2	2.0000
2024-03-03	2	2.0000
2024-03-04	3	2.2500
2024-03-05	2	2.2000
2024-03-06	1	2.0000
2024-03-07	2	2.0000
2024-03-08	2	2.0000
2024-03-09	2	2.0000

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique event record ID
user_id	INT	User who generated the event; may be NULL for anonymous events
event_name	VARCHAR(50)	Type of user event
event_time	TIMESTAMP	Timestamp when the event occurred

Sample Data

Example 1

Input

{"user_events":[["5","103","login","2024-03-03 08:00:00"],["1","101","login","2024-03-01 09:00:00"],["11","105","login","2024-03-05 12:00:00"],["3","101","purchase","2024-03-02 11:00:00"],["8","104","login","2024-03-04 10:30:00"],["14",null,"app_open","2024-03-06 08:00:00"],["18","108","login","2024-03-08 14:00:00"],["20","109","login","2024-03-09 09:00:00"],["2","102","app_open","2024-03-01 10:00:00"],["4","102","login","2024-03-02 15:00:00"],["6","103","purchase","2024-03-03 09:00:00"],["7","1

Output

[["2024-02-29","1","1.0000"],["2024-03-01","2","1.5000"],["2024-03-02","2","1.6667"],["2024-03-03","2","1.7500"],["2024-03-04","2","1.8000"],["2024-03-05","2","1.8333"],["2024-03-06","1","1.7143"],["2024-03-07","2","1.8571"],["2024-03-08","2","1.8571"],["2024-03-09","2","1.8571"],["2024-03-11","2","1.8571"]]

Practice SQL

PostgreSQL

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

Up next

7-Day Rolling DAU on FacebookHard

Daily User Engagement MetricsEasy

Compare Weekly User Activity TrendsMedium

Next question