Classify Power Users by Activity | Dataford Interview Questions

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.

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

Your question is Classify Power Users by Activity. Start with the requirements and the two tables 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