Interview Guides

Bucket Users by Order Count | Dataford Interview Questions - Dataford - Ace your Interview

Bucket Users by Order Count

Easy

SQL & Data Manipulation

Asked at 3 companies3Group ByCase WhenData Wrangling

Also asked at

Problem

Task

ShopWave wants a simple user segmentation report based on how many orders each user has placed. Write a PostgreSQL query that buckets users into segments using CASE WHEN.

Requirements

Create a user_segment column using these rules based on order_count:
- New for order_count = 0
- Light for order_count between 1 and 4
- Active for order_count between 5 and 9
- Power for order_count >= 10
- Unknown when order_count is NULL
Return the number of users in each segment as user_count, ordered by user_count descending and then user_segment ascending.

Table Definition

users

Column	Type	Description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Acquisition channel

Sample Data

user_id	user_name	order_count	signup_source
3	Carla	10	referral
1	Alice	0	ads
8	Hana	1	referral
5	Eva	NULL	organic
2	Ben	4	organic
10	Jade	9	ads
6	Finn	5	ads
4	Diego	11	organic
7	Gina	2	ads
9	Ivan	0	organic

Expected Output

user_segment	user_count
Light	3
New	2
Power	2
Active	2
Unknown	1

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Marketing channel where the user signed up

Sample Data

Example 1

Input

{"users":[[3,"Carla",10,"referral"],[1,"Alice",0,"ads"],[8,"Hana",1,"referral"],[5,"Eva",null,"organic"],[2,"Ben",4,"organic"],[10,"Jade",9,"ads"],[6,"Finn",5,"ads"],[4,"Diego",11,"organic"],[7,"Gina",2,"ads"],[9,"Ivan",0,"organic"],["1","Eva","65","organic"],["2","Gina","62","ads"],["3","Eva","33","ads"],["4","Ben","41","organic"],["5","Gina","69","referral"],["6","Diego","31","organic"],["7","Ivan","47","ads"],["8","Diego","null","ads"],["9","Diego","56","ads"],["10","Ivan","6","referral"],["1

Output[["Power","9"],["Light","3"],["Active","2"],["New","2"],["Unknown","2"]]

Problem

Task

ShopWave wants a simple user segmentation report based on how many orders each user has placed. Write a PostgreSQL query that buckets users into segments using CASE WHEN.

Requirements

Create a user_segment column using these rules based on order_count:
- New for order_count = 0
- Light for order_count between 1 and 4
- Active for order_count between 5 and 9
- Power for order_count >= 10
- Unknown when order_count is NULL
Return the number of users in each segment as user_count, ordered by user_count descending and then user_segment ascending.

Table Definition

users

Column	Type	Description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Acquisition channel

Sample Data

user_id	user_name	order_count	signup_source
3	Carla	10	referral
1	Alice	0	ads
8	Hana	1	referral
5	Eva	NULL	organic
2	Ben	4	organic
10	Jade	9	ads
6	Finn	5	ads
4	Diego	11	organic
7	Gina	2	ads
9	Ivan	0	organic

Expected Output

user_segment	user_count
Light	3
New	2
Power	2
Active	2
Unknown	1

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Marketing channel where the user signed up

Sample Data

Example 1

Input

{"users":[[3,"Carla",10,"referral"],[1,"Alice",0,"ads"],[8,"Hana",1,"referral"],[5,"Eva",null,"organic"],[2,"Ben",4,"organic"],[10,"Jade",9,"ads"],[6,"Finn",5,"ads"],[4,"Diego",11,"organic"],[7,"Gina",2,"ads"],[9,"Ivan",0,"organic"],["1","Eva","65","organic"],["2","Gina","62","ads"],["3","Eva","33","ads"],["4","Ben","41","organic"],["5","Gina","69","referral"],["6","Diego","31","organic"],["7","Ivan","47","ads"],["8","Diego","null","ads"],["9","Diego","56","ads"],["10","Ivan","6","referral"],["1

Output[["Power","9"],["Light","3"],["Active","2"],["New","2"],["Unknown","2"]]

Practice SQL

PostgreSQL

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

Up next

Classify Power Users by ActivityMedium ISegment Orders by Delivery OutcomeEasy

Weekly Active Purchaser Growth RateMedium

Next question

Bucket Users by Order Count

Easy

SQL & Data Manipulation

Asked at 3 companies3Group ByCase WhenData Wrangling

Also asked at

Problem

Task

ShopWave wants a simple user segmentation report based on how many orders each user has placed. Write a PostgreSQL query that buckets users into segments using CASE WHEN.

Requirements

Create a user_segment column using these rules based on order_count:
- New for order_count = 0
- Light for order_count between 1 and 4
- Active for order_count between 5 and 9
- Power for order_count >= 10
- Unknown when order_count is NULL
Return the number of users in each segment as user_count, ordered by user_count descending and then user_segment ascending.

Table Definition

users

Column	Type	Description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Acquisition channel

Sample Data

user_id	user_name	order_count	signup_source
3	Carla	10	referral
1	Alice	0	ads
8	Hana	1	referral
5	Eva	NULL	organic
2	Ben	4	organic
10	Jade	9	ads
6	Finn	5	ads
4	Diego	11	organic
7	Gina	2	ads
9	Ivan	0	organic

Expected Output

user_segment	user_count
Light	3
New	2
Power	2
Active	2
Unknown	1

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Marketing channel where the user signed up

Sample Data

Example 1

Input

{"users":[[3,"Carla",10,"referral"],[1,"Alice",0,"ads"],[8,"Hana",1,"referral"],[5,"Eva",null,"organic"],[2,"Ben",4,"organic"],[10,"Jade",9,"ads"],[6,"Finn",5,"ads"],[4,"Diego",11,"organic"],[7,"Gina",2,"ads"],[9,"Ivan",0,"organic"],["1","Eva","65","organic"],["2","Gina","62","ads"],["3","Eva","33","ads"],["4","Ben","41","organic"],["5","Gina","69","referral"],["6","Diego","31","organic"],["7","Ivan","47","ads"],["8","Diego","null","ads"],["9","Diego","56","ads"],["10","Ivan","6","referral"],["1

Output[["Power","9"],["Light","3"],["Active","2"],["New","2"],["Unknown","2"]]

Problem

Task

ShopWave wants a simple user segmentation report based on how many orders each user has placed. Write a PostgreSQL query that buckets users into segments using CASE WHEN.

Requirements

Create a user_segment column using these rules based on order_count:
- New for order_count = 0
- Light for order_count between 1 and 4
- Active for order_count between 5 and 9
- Power for order_count >= 10
- Unknown when order_count is NULL
Return the number of users in each segment as user_count, ordered by user_count descending and then user_segment ascending.

Table Definition

users

Column	Type	Description
user_id	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Acquisition channel

Sample Data

user_id	user_name	order_count	signup_source
3	Carla	10	referral
1	Alice	0	ads
8	Hana	1	referral
5	Eva	NULL	organic
2	Ben	4	organic
10	Jade	9	ads
6	Finn	5	ads
4	Diego	11	organic
7	Gina	2	ads
9	Ivan	0	organic

Expected Output

user_segment	user_count
Light	3
New	2
Power	2
Active	2
Unknown	1

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for each user
user_name	VARCHAR(100)	User name
order_count	INT	Total number of orders placed by the user
signup_source	VARCHAR(50)	Marketing channel where the user signed up

Sample Data

Example 1

Input

{"users":[[3,"Carla",10,"referral"],[1,"Alice",0,"ads"],[8,"Hana",1,"referral"],[5,"Eva",null,"organic"],[2,"Ben",4,"organic"],[10,"Jade",9,"ads"],[6,"Finn",5,"ads"],[4,"Diego",11,"organic"],[7,"Gina",2,"ads"],[9,"Ivan",0,"organic"],["1","Eva","65","organic"],["2","Gina","62","ads"],["3","Eva","33","ads"],["4","Ben","41","organic"],["5","Gina","69","referral"],["6","Diego","31","organic"],["7","Ivan","47","ads"],["8","Diego","null","ads"],["9","Diego","56","ads"],["10","Ivan","6","referral"],["1

Output[["Power","9"],["Light","3"],["Active","2"],["New","2"],["Unknown","2"]]

Practice SQL

PostgreSQL

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

Up next

Classify Power Users by ActivityMedium ISegment Orders by Delivery OutcomeEasy

Weekly Active Purchaser Growth RateMedium

Next question