Top Users by Region Query | Dataford Interview Questions

ResMed’s myAir analytics team wants to identify the highest-usage users in each geographic region. Write a PostgreSQL query that ranks users by their total completed therapy duration.

Requirements

Join users to myair_usage_sessions using user_id.
Include only sessions with session_status = 'completed' and a non-null, non-negative duration_minutes value.
Calculate total usage duration for each user within each non-null region.
Return the top 10 users per region, ranking ties deterministically by ascending user_id. Include the region, user ID, total duration, and regional rank.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique myAir user identifier
region	VARCHAR(50)	Geographic region assigned to the user

myair_usage_sessions

Column	Type	Description
session_idPK	INTEGER	Unique usage session identifier
user_id	INTEGER	Associated user identifier
session_date	DATE	Date on which the therapy session occurred
duration_minutes	INTEGER	Therapy usage duration in minutes
session_status	VARCHAR(20)	Processing status of the usage session

Your question is Top Users by Region Query. 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