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_segmentcolumn using these rules based onorder_count:Newfororder_count = 0Lightfororder_countbetween 1 and 4Activefororder_countbetween 5 and 9Powerfororder_count >= 10Unknownwhenorder_countisNULL
- Return the number of users in each segment as
user_count, ordered byuser_countdescending and thenuser_segmentascending.
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 |
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 |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
