Interview Guides

Classify Power Users by Activity | Dataford Interview Questions - Dataford - Ace your Interview

Classify Power Users by Activity

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup ByCase When

Also asked at

Problem

Task

StreamWave wants to segment users by recent activity. Write a PostgreSQL query to identify power users and compare them with casual users over the last 30 days.

A user is a power user if they completed 5 or more sessions in the last 30 days. Otherwise, they are a casual user. Only include users whose account status is active.

Requirements

Count each active user's completed sessions in the last 30 days.
Classify users as power_user or casual_user using the 5-session threshold.
Return one row per segment with:
- number of users
- average completed sessions per user
- average total minutes watched per user
Include active users with zero completed sessions as casual_user.
Order results by user_segment.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key
user_name	VARCHAR(100)	User name
signup_date	DATE	Account creation date
status	VARCHAR(20)	Account status

`sessions`

column	type	description
session_id	INT	Primary key
user_id	INT	References users.user_id
session_date	DATE	Session date
duration_minutes	INT	Session length in minutes
session_status	VARCHAR(20)	Session outcome

Sample Data

`users`

user_id	user_name	signup_date	status
1	Ava Patel	2024-12-15	active
2	Ben Carter	2025-01-10	active
3	Chloe Kim	2025-01-20	inactive
4	Diego Ruiz	2025-02-01	active

`sessions`

session_id	user_id	session_date	duration_minutes	session_status
101	1	2025-04-28	40	completed
102	1	2025-04-20	35	completed
103	2	2025-04-29	15	completed
104	4	2025-04-10	20	cancelled

Expected Output

user_segment	user_count	avg_completed_sessions	avg_total_minutes
casual_user	4	1.25	37.50
power_user	2	5.50	182.50

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user created the account
status	VARCHAR(20)	Account status such as active or inactive

sessions

Column	Type	Description
session_idPK	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
duration_minutes	INT	Length of the session in minutes
session_status	VARCHAR(20)	Status of the session such as completed or cancelled

Sample Data

Example 1

Input

{"users":[["1","Ava Patel","2024-12-15","active"],["2","Ben Carter","2025-01-10","active"],["3","Chloe Kim","2025-01-20","inactive"],["4","Diego Ruiz","2025-02-01","active"],["5","Emma Stone","2025-02-14","active"],["6","Farah Ali","2025-02-20","active"],["7","Gavin Lee","2025-03-05","active"],["8","Hana Park","2025-03-18",null],["9","Ivan Torres","2025-03-25","active"],["10","Jules Martin","2025-04-01","inactive"],["11","Maya Chen","2025-02-24","null"],["12","Ben Carter","2025-03-15","verified"

Output[["casual_user","5","1.00","12.20"],["power_user","2","5.50","172.50"]]

Problem

Task

StreamWave wants to segment users by recent activity. Write a PostgreSQL query to identify power users and compare them with casual users over the last 30 days.

A user is a power user if they completed 5 or more sessions in the last 30 days. Otherwise, they are a casual user. Only include users whose account status is active.

Requirements

Count each active user's completed sessions in the last 30 days.
Classify users as power_user or casual_user using the 5-session threshold.
Return one row per segment with:
- number of users
- average completed sessions per user
- average total minutes watched per user
Include active users with zero completed sessions as casual_user.
Order results by user_segment.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key
user_name	VARCHAR(100)	User name
signup_date	DATE	Account creation date
status	VARCHAR(20)	Account status

`sessions`

column	type	description
session_id	INT	Primary key
user_id	INT	References users.user_id
session_date	DATE	Session date
duration_minutes	INT	Session length in minutes
session_status	VARCHAR(20)	Session outcome

Sample Data

`users`

user_id	user_name	signup_date	status
1	Ava Patel	2024-12-15	active
2	Ben Carter	2025-01-10	active
3	Chloe Kim	2025-01-20	inactive
4	Diego Ruiz	2025-02-01	active

`sessions`

session_id	user_id	session_date	duration_minutes	session_status
101	1	2025-04-28	40	completed
102	1	2025-04-20	35	completed
103	2	2025-04-29	15	completed
104	4	2025-04-10	20	cancelled

Expected Output

user_segment	user_count	avg_completed_sessions	avg_total_minutes
casual_user	4	1.25	37.50
power_user	2	5.50	182.50

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user created the account
status	VARCHAR(20)	Account status such as active or inactive

sessions

Column	Type	Description
session_idPK	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
duration_minutes	INT	Length of the session in minutes
session_status	VARCHAR(20)	Status of the session such as completed or cancelled

Sample Data

Example 1

Input

{"users":[["1","Ava Patel","2024-12-15","active"],["2","Ben Carter","2025-01-10","active"],["3","Chloe Kim","2025-01-20","inactive"],["4","Diego Ruiz","2025-02-01","active"],["5","Emma Stone","2025-02-14","active"],["6","Farah Ali","2025-02-20","active"],["7","Gavin Lee","2025-03-05","active"],["8","Hana Park","2025-03-18",null],["9","Ivan Torres","2025-03-25","active"],["10","Jules Martin","2025-04-01","inactive"],["11","Maya Chen","2025-02-24","null"],["12","Ben Carter","2025-03-15","verified"

Output[["casual_user","5","1.00","12.20"],["power_user","2","5.50","172.50"]]

Practice SQL

PostgreSQL

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

Up next

Monthly User Engagement Trend AnalysisMedium

7-Day Rolling Active UsersMedium

Compare Weekly User Activity TrendsMedium

Next question

Classify Power Users by Activity

Medium

SQL & Data Manipulation

Asked at 1 company1JoinsAggregationsGroup ByCase When

Also asked at

Problem

Task

StreamWave wants to segment users by recent activity. Write a PostgreSQL query to identify power users and compare them with casual users over the last 30 days.

A user is a power user if they completed 5 or more sessions in the last 30 days. Otherwise, they are a casual user. Only include users whose account status is active.

Requirements

Count each active user's completed sessions in the last 30 days.
Classify users as power_user or casual_user using the 5-session threshold.
Return one row per segment with:
- number of users
- average completed sessions per user
- average total minutes watched per user
Include active users with zero completed sessions as casual_user.
Order results by user_segment.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key
user_name	VARCHAR(100)	User name
signup_date	DATE	Account creation date
status	VARCHAR(20)	Account status

`sessions`

column	type	description
session_id	INT	Primary key
user_id	INT	References users.user_id
session_date	DATE	Session date
duration_minutes	INT	Session length in minutes
session_status	VARCHAR(20)	Session outcome

Sample Data

`users`

user_id	user_name	signup_date	status
1	Ava Patel	2024-12-15	active
2	Ben Carter	2025-01-10	active
3	Chloe Kim	2025-01-20	inactive
4	Diego Ruiz	2025-02-01	active

`sessions`

session_id	user_id	session_date	duration_minutes	session_status
101	1	2025-04-28	40	completed
102	1	2025-04-20	35	completed
103	2	2025-04-29	15	completed
104	4	2025-04-10	20	cancelled

Expected Output

user_segment	user_count	avg_completed_sessions	avg_total_minutes
casual_user	4	1.25	37.50
power_user	2	5.50	182.50

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user created the account
status	VARCHAR(20)	Account status such as active or inactive

sessions

Column	Type	Description
session_idPK	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
duration_minutes	INT	Length of the session in minutes
session_status	VARCHAR(20)	Status of the session such as completed or cancelled

Sample Data

Example 1

Input

{"users":[["1","Ava Patel","2024-12-15","active"],["2","Ben Carter","2025-01-10","active"],["3","Chloe Kim","2025-01-20","inactive"],["4","Diego Ruiz","2025-02-01","active"],["5","Emma Stone","2025-02-14","active"],["6","Farah Ali","2025-02-20","active"],["7","Gavin Lee","2025-03-05","active"],["8","Hana Park","2025-03-18",null],["9","Ivan Torres","2025-03-25","active"],["10","Jules Martin","2025-04-01","inactive"],["11","Maya Chen","2025-02-24","null"],["12","Ben Carter","2025-03-15","verified"

Output[["casual_user","5","1.00","12.20"],["power_user","2","5.50","172.50"]]

Problem

Task

StreamWave wants to segment users by recent activity. Write a PostgreSQL query to identify power users and compare them with casual users over the last 30 days.

A user is a power user if they completed 5 or more sessions in the last 30 days. Otherwise, they are a casual user. Only include users whose account status is active.

Requirements

Count each active user's completed sessions in the last 30 days.
Classify users as power_user or casual_user using the 5-session threshold.
Return one row per segment with:
- number of users
- average completed sessions per user
- average total minutes watched per user
Include active users with zero completed sessions as casual_user.
Order results by user_segment.

Table Definitions

`users`

column	type	description
user_id	INT	Primary key
user_name	VARCHAR(100)	User name
signup_date	DATE	Account creation date
status	VARCHAR(20)	Account status

`sessions`

column	type	description
session_id	INT	Primary key
user_id	INT	References users.user_id
session_date	DATE	Session date
duration_minutes	INT	Session length in minutes
session_status	VARCHAR(20)	Session outcome

Sample Data

`users`

user_id	user_name	signup_date	status
1	Ava Patel	2024-12-15	active
2	Ben Carter	2025-01-10	active
3	Chloe Kim	2025-01-20	inactive
4	Diego Ruiz	2025-02-01	active

`sessions`

session_id	user_id	session_date	duration_minutes	session_status
101	1	2025-04-28	40	completed
102	1	2025-04-20	35	completed
103	2	2025-04-29	15	completed
104	4	2025-04-10	20	cancelled

Expected Output

user_segment	user_count	avg_completed_sessions	avg_total_minutes
casual_user	4	1.25	37.50
power_user	2	5.50	182.50

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	Full name of the user
signup_date	DATE	Date the user created the account
status	VARCHAR(20)	Account status such as active or inactive

sessions

Column	Type	Description
session_idPK	INT	Primary key for each session
user_id	INT	User who started the session
session_date	DATE	Date of the session
duration_minutes	INT	Length of the session in minutes
session_status	VARCHAR(20)	Status of the session such as completed or cancelled

Sample Data

Example 1

Input

{"users":[["1","Ava Patel","2024-12-15","active"],["2","Ben Carter","2025-01-10","active"],["3","Chloe Kim","2025-01-20","inactive"],["4","Diego Ruiz","2025-02-01","active"],["5","Emma Stone","2025-02-14","active"],["6","Farah Ali","2025-02-20","active"],["7","Gavin Lee","2025-03-05","active"],["8","Hana Park","2025-03-18",null],["9","Ivan Torres","2025-03-25","active"],["10","Jules Martin","2025-04-01","inactive"],["11","Maya Chen","2025-02-24","null"],["12","Ben Carter","2025-03-15","verified"

Output[["casual_user","5","1.00","12.20"],["power_user","2","5.50","172.50"]]

Practice SQL

PostgreSQL

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

Up next

Monthly User Engagement Trend AnalysisMedium

7-Day Rolling Active UsersMedium

Compare Weekly User Activity TrendsMedium

Next question