Problem
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_userorcasual_userusing 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 |
Schema
users
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Primary key for each user |
| user_name | VARCHAR(100) | Full name of the user |
| signup_date | DATE | Date the user created the account |
| status | VARCHAR(20) | Account status such as active or inactive |
sessions
| Column | Type | Description |
|---|---|---|
| session_idPK | INT | Primary key for each session |
| user_id | INT | User who started the session |
| session_date | DATE | Date of the session |
| duration_minutes | INT | Length of the session in minutes |
| session_status | VARCHAR(20) | Status of the session such as completed or cancelled |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
