Top Users by Session Duration | Dataford Interview Questions

Write a query to identify the top three users by session duration in this schema.

Use the users and sessions tables. Consider only completed sessions, where ended_at is not NULL. Session duration should be measured in seconds.

Output

One row per user, with columns user_id, username, and total_session_duration_seconds.
Include only the three users with the greatest total completed session duration.
Order by total duration descending, using user_id ascending to break ties.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(100)	User display name
email	VARCHAR(255)	User email address

sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
user_id	INT	User associated with the session
started_at	TIMESTAMP	Session start timestamp
ended_at	TIMESTAMP	Session end timestamp, NULL for ongoing sessions
device_type	VARCHAR(30)	Device used during the session

Your question is Top Users by Session Duration. 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