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 = 0Light for order_count between 1 and 4Active for order_count between 5 and 9Power for order_count >= 10Unknown 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 |
