Bucket Users by Order Count | Dataford Interview Questions

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.

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

Your question is Bucket Users by Order Count. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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

Your question is Bucket Users by Order Count. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.