Top Ten Longest Active Sessions | Dataford Interview Questions

Given a table of client login events, write a query to identify the top ten longest continuous active sessions. A session is continuous only when a login event is immediately followed by a logout event for the same client. Ignore events with a NULL timestamp.

Output

One row per valid session with client_id, session_start, session_end, and duration_minutes.
Include at most ten sessions, ordered by duration descending, then client_id ascending and session start ascending for ties.
Rows with a NULL timestamp are excluded.

Schema

client_login_events

Column	Type	Description
event_idPK	INTEGER	Unique identifier for the login event
client_id	VARCHAR(20)	Identifier of the client generating the event
event_type	VARCHAR(30)	Event action, such as login, logout, or another activity
event_timestamp	TIMESTAMP	Timestamp when the event occurred

Your question is Top Ten Longest Active Sessions. Start with the requirements and the one table 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

Output

One row per valid session with client_id, session_start, session_end, and duration_minutes.
Include at most ten sessions, ordered by duration descending, then client_id ascending and session start ascending for ties.
Rows with a NULL timestamp are excluded.

Schema

client_login_events

Column	Type	Description
event_idPK	INTEGER	Unique identifier for the login event
client_id	VARCHAR(20)	Identifier of the client generating the event
event_type	VARCHAR(30)	Event action, such as login, logout, or another activity
event_timestamp	TIMESTAMP	Timestamp when the event occurred

Your question is Top Ten Longest Active Sessions. Start with the requirements and the one table 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.