Top 3 Longest Sessions Per User | Dataford Interview Questions

Anduril operators use Lattice to monitor and coordinate activity across operational surfaces. The analytics team wants to identify the longest sessions for each user.

Write a PostgreSQL query that returns the top three completed sessions for every user, ranked by session duration.

Requirements

Calculate each completed session's duration in whole seconds from started_at and ended_at.
Rank sessions independently for each user, with the longest sessions ranked first. Break duration ties by the smaller session_id.
Return only ranks 1 through 3, including the user's name, session ID, Lattice surface, and duration.
Exclude sessions with a NULL ended_at value and users without completed sessions.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
user_name	VARCHAR(100)	Operator display name

session_logs

Column	Type	Description
session_idPK	INTEGER	Unique session identifier
user_id	INTEGER	References users.user_id
started_at	TIMESTAMPTZ	Session start timestamp
ended_at	TIMESTAMPTZ	Session end timestamp, or NULL while active
surface	VARCHAR(80)	Lattice surface used during the session

Your question is Top 3 Longest Sessions Per User. 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