Window Functions for Top Users | Dataford Interview Questions

Your question is Window Functions for Top Users. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

LexisNexis Legal & Professional wants to understand engagement with Lexis+. Write a PostgreSQL query that identifies the three users with the greatest total session duration within each region.

Requirements

Join users to sessions and calculate each user's total positive session duration in seconds.
Exclude users whose region is unknown and sessions with zero or NULL duration.
Rank users independently within each region using a window function. Break duration ties by ascending user_id, and return only ranks 1 through 3.
Return the region, user ID, user name, total duration, and regional rank, ordered by region and rank.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	User display name
region	VARCHAR(50)	User geographic region

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
session_duration_seconds	INT	Session duration in seconds