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 |
