Interview Guides

Top Active Users by Event Count | Dataford Interview Questions - Dataford - Ace your Interview

Top Active Users by Event Count

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByRanking

Also asked at

Problem

Task

StreamHub wants to identify its most active users based on recorded app events. Write a SQL query to return the top N active users, where activity is measured by the number of valid events tied to active user accounts.

Requirements

Count events per user using the events table.
Only include users whose account_status = 'active'.
Exclude events where event_type is NULL.
Return the top 3 users ranked by total event count, ordered by event count descending and then user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Account state such as active, inactive, or suspended
signup_date	DATE	Date the user registered

`events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of event such as login or play
event_time	TIMESTAMP	Time the event occurred

Sample Data

`users`

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15

`events`

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	Display name of the user
account_status	VARCHAR(20)	Current account status such as active, inactive, or suspended
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of event generated by the user
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15
108	Hugo Martin		2024-02-18
105	Ethan Cole	suspended	2024-02-01
107	Grace Lee	active	2024-02-12
109	Ivy Chen	active	2024-02-22
1	Jack Turner	expired	2024-01-07
2	Priya Nair	fraud_hold	2024-02-04
3	Sam Walker	churned	2024-01-25
4	Tina Zhou	active	2024-01-06
5	Xavier Price	archived	2024-01-03
6	Jack Turner	paused	2024-01-11
7	Wendy Lin	null	2024-02-23
8	Sam Walker	inactive	2024-01-12
9	Carlos Ruiz	premium	2024-02-10
10	Uma Shah	guest	2024-02-19
11	Uma Shah	deleted	2024-01-28
12	Maya Scott	premium	2024-01-14
13	Grace Lee	guest	2024-02-11
14	Ivy Chen	verified	2024-01-15
15	Xavier Price	pending	2024-02-23
16	Bob Singh	vip	2024-02-05
17	Tina Zhou	unverified	2024-02-18
18	Ethan Cole	cancelled	2024-01-16
19	Zane Cooper	verified	2024-02-15
20	Ava Mitchell	pending	2024-01-25

events

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00
1	101	login	2024-03-01 08:00:00
3	101	search	2024-03-01 08:10:00
4	101	play	2024-03-01 08:15:00
6	102	play	2024-03-01 09:05:00
7	103	login	2024-03-02 08:55:00
9	103	play	2024-03-02 09:05:00
10	103	share	2024-03-02 09:10:00
12	104	login	2024-03-03 10:05:00
13	104	play	2024-03-03 10:10:00
15	106		2024-03-04 07:05:00
16	107	login	2024-03-04 08:00:00
17	107	play	2024-03-04 08:10:00
18	105	login	2024-03-04 09:00:00
19	999	play	2024-03-04 09:10:00
20		login	2024-03-04 09:15:00
21	109		2024-03-04 10:00:00
22	101		2024-03-05 08:00:00
1	54	pause	2024-03-06 07:07:58
2	84	upload	2024-03-06 10:22:17
3	88	unfollow	2024-03-03 21:02:57
4	32	download	2024-03-04 22:02:59
5	59	logout	2024-03-05 20:12:37

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Problem

Task

Requirements

Count events per user using the events table.
Only include users whose account_status = 'active'.
Exclude events where event_type is NULL.
Return the top 3 users ranked by total event count, ordered by event count descending and then user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Account state such as active, inactive, or suspended
signup_date	DATE	Date the user registered

`events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of event such as login or play
event_time	TIMESTAMP	Time the event occurred

Sample Data

`users`

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15

`events`

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	Display name of the user
account_status	VARCHAR(20)	Current account status such as active, inactive, or suspended
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of event generated by the user
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15
108	Hugo Martin		2024-02-18
105	Ethan Cole	suspended	2024-02-01
107	Grace Lee	active	2024-02-12
109	Ivy Chen	active	2024-02-22
1	Jack Turner	expired	2024-01-07
2	Priya Nair	fraud_hold	2024-02-04
3	Sam Walker	churned	2024-01-25
4	Tina Zhou	active	2024-01-06
5	Xavier Price	archived	2024-01-03
6	Jack Turner	paused	2024-01-11
7	Wendy Lin	null	2024-02-23
8	Sam Walker	inactive	2024-01-12
9	Carlos Ruiz	premium	2024-02-10
10	Uma Shah	guest	2024-02-19
11	Uma Shah	deleted	2024-01-28
12	Maya Scott	premium	2024-01-14
13	Grace Lee	guest	2024-02-11
14	Ivy Chen	verified	2024-01-15
15	Xavier Price	pending	2024-02-23
16	Bob Singh	vip	2024-02-05
17	Tina Zhou	unverified	2024-02-18
18	Ethan Cole	cancelled	2024-01-16
19	Zane Cooper	verified	2024-02-15
20	Ava Mitchell	pending	2024-01-25

events

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00
1	101	login	2024-03-01 08:00:00
3	101	search	2024-03-01 08:10:00
4	101	play	2024-03-01 08:15:00
6	102	play	2024-03-01 09:05:00
7	103	login	2024-03-02 08:55:00
9	103	play	2024-03-02 09:05:00
10	103	share	2024-03-02 09:10:00
12	104	login	2024-03-03 10:05:00
13	104	play	2024-03-03 10:10:00
15	106		2024-03-04 07:05:00
16	107	login	2024-03-04 08:00:00
17	107	play	2024-03-04 08:10:00
18	105	login	2024-03-04 09:00:00
19	999	play	2024-03-04 09:10:00
20		login	2024-03-04 09:15:00
21	109		2024-03-04 10:00:00
22	101		2024-03-05 08:00:00
1	54	pause	2024-03-06 07:07:58
2	84	upload	2024-03-06 10:22:17
3	88	unfollow	2024-03-03 21:02:57
4	32	download	2024-03-04 22:02:59
5	59	logout	2024-03-05 20:12:37

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Practice SQL

PostgreSQL

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

Up next

Daily User Engagement MetricsEasy

First and Last User EventsMedium

7-Day Rolling Active UsersMedium

Next question

Top Active Users by Event Count

Medium

SQL & Data Manipulation

Asked at 1 company1AggregationsGroup ByRanking

Also asked at

Problem

Task

Requirements

Count events per user using the events table.
Only include users whose account_status = 'active'.
Exclude events where event_type is NULL.
Return the top 3 users ranked by total event count, ordered by event count descending and then user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Account state such as active, inactive, or suspended
signup_date	DATE	Date the user registered

`events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of event such as login or play
event_time	TIMESTAMP	Time the event occurred

Sample Data

`users`

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15

`events`

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	Display name of the user
account_status	VARCHAR(20)	Current account status such as active, inactive, or suspended
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of event generated by the user
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15
108	Hugo Martin		2024-02-18
105	Ethan Cole	suspended	2024-02-01
107	Grace Lee	active	2024-02-12
109	Ivy Chen	active	2024-02-22
1	Jack Turner	expired	2024-01-07
2	Priya Nair	fraud_hold	2024-02-04
3	Sam Walker	churned	2024-01-25
4	Tina Zhou	active	2024-01-06
5	Xavier Price	archived	2024-01-03
6	Jack Turner	paused	2024-01-11
7	Wendy Lin	null	2024-02-23
8	Sam Walker	inactive	2024-01-12
9	Carlos Ruiz	premium	2024-02-10
10	Uma Shah	guest	2024-02-19
11	Uma Shah	deleted	2024-01-28
12	Maya Scott	premium	2024-01-14
13	Grace Lee	guest	2024-02-11
14	Ivy Chen	verified	2024-01-15
15	Xavier Price	pending	2024-02-23
16	Bob Singh	vip	2024-02-05
17	Tina Zhou	unverified	2024-02-18
18	Ethan Cole	cancelled	2024-01-16
19	Zane Cooper	verified	2024-02-15
20	Ava Mitchell	pending	2024-01-25

events

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00
1	101	login	2024-03-01 08:00:00
3	101	search	2024-03-01 08:10:00
4	101	play	2024-03-01 08:15:00
6	102	play	2024-03-01 09:05:00
7	103	login	2024-03-02 08:55:00
9	103	play	2024-03-02 09:05:00
10	103	share	2024-03-02 09:10:00
12	104	login	2024-03-03 10:05:00
13	104	play	2024-03-03 10:10:00
15	106		2024-03-04 07:05:00
16	107	login	2024-03-04 08:00:00
17	107	play	2024-03-04 08:10:00
18	105	login	2024-03-04 09:00:00
19	999	play	2024-03-04 09:10:00
20		login	2024-03-04 09:15:00
21	109		2024-03-04 10:00:00
22	101		2024-03-05 08:00:00
1	54	pause	2024-03-06 07:07:58
2	84	upload	2024-03-06 10:22:17
3	88	unfollow	2024-03-03 21:02:57
4	32	download	2024-03-04 22:02:59
5	59	logout	2024-03-05 20:12:37

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Problem

Task

Requirements

Count events per user using the events table.
Only include users whose account_status = 'active'.
Exclude events where event_type is NULL.
Return the top 3 users ranked by total event count, ordered by event count descending and then user_id ascending.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User display name
account_status	VARCHAR(20)	Account state such as active, inactive, or suspended
signup_date	DATE	Date the user registered

`events`

column	type	description
event_id	INT	Primary key for each event
user_id	INT	User who generated the event
event_type	VARCHAR(50)	Type of event such as login or play
event_time	TIMESTAMP	Time the event occurred

Sample Data

`users`

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15

`events`

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for each user
user_name	VARCHAR(100)	Display name of the user
account_status	VARCHAR(20)	Current account status such as active, inactive, or suspended
signup_date	DATE	Date the user signed up

events

Column	Type	Description
event_idPK	INT	Unique identifier for each event
user_id	INT	User associated with the event
event_type	VARCHAR(50)	Type of event generated by the user
event_time	TIMESTAMP	Timestamp when the event occurred

users

user_id	user_name	account_status	signup_date
104	Diana Park	active	2024-01-20
101	Alice Chen	active	2024-01-05
106	Farah Khan	active	2024-02-10
102	Bob Singh	inactive	2024-01-08
103	Carlos Ruiz	active	2024-01-15
108	Hugo Martin		2024-02-18
105	Ethan Cole	suspended	2024-02-01
107	Grace Lee	active	2024-02-12
109	Ivy Chen	active	2024-02-22
1	Jack Turner	expired	2024-01-07
2	Priya Nair	fraud_hold	2024-02-04
3	Sam Walker	churned	2024-01-25
4	Tina Zhou	active	2024-01-06
5	Xavier Price	archived	2024-01-03
6	Jack Turner	paused	2024-01-11
7	Wendy Lin	null	2024-02-23
8	Sam Walker	inactive	2024-01-12
9	Carlos Ruiz	premium	2024-02-10
10	Uma Shah	guest	2024-02-19
11	Uma Shah	deleted	2024-01-28
12	Maya Scott	premium	2024-01-14
13	Grace Lee	guest	2024-02-11
14	Ivy Chen	verified	2024-01-15
15	Xavier Price	pending	2024-02-23
16	Bob Singh	vip	2024-02-05
17	Tina Zhou	unverified	2024-02-18
18	Ethan Cole	cancelled	2024-01-16
19	Zane Cooper	verified	2024-02-15
20	Ava Mitchell	pending	2024-01-25

events

event_id	user_id	event_type	event_time
11	104	play	2024-03-03 10:00:00
2	101	play	2024-03-01 08:05:00
8	103	like	2024-03-02 09:00:00
14	106	login	2024-03-04 07:00:00
5	102	login	2024-03-01 09:00:00
1	101	login	2024-03-01 08:00:00
3	101	search	2024-03-01 08:10:00
4	101	play	2024-03-01 08:15:00
6	102	play	2024-03-01 09:05:00
7	103	login	2024-03-02 08:55:00
9	103	play	2024-03-02 09:05:00
10	103	share	2024-03-02 09:10:00
12	104	login	2024-03-03 10:05:00
13	104	play	2024-03-03 10:10:00
15	106		2024-03-04 07:05:00
16	107	login	2024-03-04 08:00:00
17	107	play	2024-03-04 08:10:00
18	105	login	2024-03-04 09:00:00
19	999	play	2024-03-04 09:10:00
20		login	2024-03-04 09:15:00
21	109		2024-03-04 10:00:00
22	101		2024-03-05 08:00:00
1	54	pause	2024-03-06 07:07:58
2	84	upload	2024-03-06 10:22:17
3	88	unfollow	2024-03-03 21:02:57
4	32	download	2024-03-04 22:02:59
5	59	logout	2024-03-05 20:12:37

Expected Output

user_id	user_name	total_events
101	Alice Chen	4
103	Carlos Ruiz	4
104	Diana Park	3

Practice SQL

PostgreSQL

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

Up next

Daily User Engagement MetricsEasy

First and Last User EventsMedium

7-Day Rolling Active UsersMedium

Next question