Interview Guides

Daily User Engagement Metrics

Easy

SQL & Data Manipulation

Asked at 12 companies12JoinsAggregationsGroup By

Asked 2mo ago|Ancestry Marketing

Also asked at

Problem

Task

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event; may be NULL for anonymous activity
event_type	VARCHAR(50)	Type of event such as login, click, or purchase
event_date	DATE	Date the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_type	event_date	device_type
3	102	click	2024-03-01	web
1	101	login	2024-03-01	mobile
8	104	login	2024-03-03	mobile
5	101	purchase	2024-03-02	mobile
10	NULL	view	2024-03-04	web
12	106	login	2024-03-05	tablet

Expected Output

event_date	total_events	active_users
2024-03-01	3	2
2024-03-02	3	2
2024-03-03	2	2
2024-03-04	2	1
2024-03-05	2	2

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"user_events":[["3","102","click","2024-03-01","web"],["1","101","login","2024-03-01","mobile"],["14","107","login","2024-02-29","web"],["2","101","view","2024-03-01","mobile"],["6","103","login","2024-03-02","web"],["4","102","login","2024-03-02","tablet"],["5","101","purchase","2024-03-02","mobile"],["8","104","login","2024-03-03","mobile"],["7","103","click","2024-03-03","web"],["10",null,"view","2024-03-04","web"],["9","104","click","2024-03-04",null],["12","106","login","2024-03-05","table

Output[["2024-03-01","4","2"],["2024-03-02","3","3"],["2024-03-03","2","2"],["2024-03-04","6","2"],["2024-03-05","3","2"]]

Problem

Task

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event; may be NULL for anonymous activity
event_type	VARCHAR(50)	Type of event such as login, click, or purchase
event_date	DATE	Date the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_type	event_date	device_type
3	102	click	2024-03-01	web
1	101	login	2024-03-01	mobile
8	104	login	2024-03-03	mobile
5	101	purchase	2024-03-02	mobile
10	NULL	view	2024-03-04	web
12	106	login	2024-03-05	tablet

Expected Output

event_date	total_events	active_users
2024-03-01	3	2
2024-03-02	3	2
2024-03-03	2	2
2024-03-04	2	1
2024-03-05	2	2

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"user_events":[["3","102","click","2024-03-01","web"],["1","101","login","2024-03-01","mobile"],["14","107","login","2024-02-29","web"],["2","101","view","2024-03-01","mobile"],["6","103","login","2024-03-02","web"],["4","102","login","2024-03-02","tablet"],["5","101","purchase","2024-03-02","mobile"],["8","104","login","2024-03-03","mobile"],["7","103","click","2024-03-03","web"],["10",null,"view","2024-03-04","web"],["9","104","click","2024-03-04",null],["12","106","login","2024-03-05","table

Output[["2024-03-01","4","2"],["2024-03-02","3","3"],["2024-03-03","2","2"],["2024-03-04","6","2"],["2024-03-05","3","2"]]

Practice SQL

PostgreSQL

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

Daily User Engagement Metrics

Easy

SQL & Data Manipulation

Asked at 12 companies12JoinsAggregationsGroup By

Asked 2mo ago|Ancestry Marketing

Also asked at

Problem

Task

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event; may be NULL for anonymous activity
event_type	VARCHAR(50)	Type of event such as login, click, or purchase
event_date	DATE	Date the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_type	event_date	device_type
3	102	click	2024-03-01	web
1	101	login	2024-03-01	mobile
8	104	login	2024-03-03	mobile
5	101	purchase	2024-03-02	mobile
10	NULL	view	2024-03-04	web
12	106	login	2024-03-05	tablet

Expected Output

event_date	total_events	active_users
2024-03-01	3	2
2024-03-02	3	2
2024-03-03	2	2
2024-03-04	2	1
2024-03-05	2	2

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"user_events":[["3","102","click","2024-03-01","web"],["1","101","login","2024-03-01","mobile"],["14","107","login","2024-02-29","web"],["2","101","view","2024-03-01","mobile"],["6","103","login","2024-03-02","web"],["4","102","login","2024-03-02","tablet"],["5","101","purchase","2024-03-02","mobile"],["8","104","login","2024-03-03","mobile"],["7","103","click","2024-03-03","web"],["10",null,"view","2024-03-04","web"],["9","104","click","2024-03-04",null],["12","106","login","2024-03-05","table

Output[["2024-03-01","4","2"],["2024-03-02","3","3"],["2024-03-03","2","2"],["2024-03-04","6","2"],["2024-03-05","3","2"]]

Problem

Task

StreamLine wants a simple daily engagement report from its app event log. Write a SQL query to calculate user engagement metrics for a specific date range using only the user_events table.

Requirements

Return one row per event_date for dates between 2024-03-01 and 2024-03-05 inclusive.
For each date, calculate:
- total_events: total number of event rows
- active_users: count of distinct non-null user_id values
Sort the result by event_date ascending.

Table Definition

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event; may be NULL for anonymous activity
event_type	VARCHAR(50)	Type of event such as login, click, or purchase
event_date	DATE	Date the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

event_id	user_id	event_type	event_date	device_type
3	102	click	2024-03-01	web
1	101	login	2024-03-01	mobile
8	104	login	2024-03-03	mobile
5	101	purchase	2024-03-02	mobile
10	NULL	view	2024-03-04	web
12	106	login	2024-03-05	tablet

Expected Output

event_date	total_events	active_users
2024-03-01	3	2
2024-03-02	3	2
2024-03-03	2	2
2024-03-04	2	1
2024-03-05	2	2

Schema

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User who triggered the event; NULL for anonymous activity
event_type	VARCHAR(50)	Type of user event
event_date	DATE	Calendar date when the event occurred
device_type	VARCHAR(20)	Device used for the event

Sample Data

Example 1

Input

{"user_events":[["3","102","click","2024-03-01","web"],["1","101","login","2024-03-01","mobile"],["14","107","login","2024-02-29","web"],["2","101","view","2024-03-01","mobile"],["6","103","login","2024-03-02","web"],["4","102","login","2024-03-02","tablet"],["5","101","purchase","2024-03-02","mobile"],["8","104","login","2024-03-03","mobile"],["7","103","click","2024-03-03","web"],["10",null,"view","2024-03-04","web"],["9","104","click","2024-03-04",null],["12","106","login","2024-03-05","table

Output[["2024-03-01","4","2"],["2024-03-02","3","3"],["2024-03-03","2","2"],["2024-03-04","6","2"],["2024-03-05","3","2"]]

Practice SQL

PostgreSQL

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

Daily User Engagement Metrics | Dataford Interview Questions - Dataford - Ace your Interview