Average Session Duration Query | Dataford Interview Questions

Given a table of user login timestamps, write a query to calculate the average session duration, defining a session break as 30 minutes of inactivity at Twilio.

Use user_logins. A new session begins only when the gap is greater than 30 minutes. Exclude rows with null timestamps, calculate each session from its first to last login, and return the average duration in minutes.

Output

Return one row with average_session_duration_minutes.
The result must be a numeric average across all users' sessions.

Schema

user_logins

Column	Type	Description
login_idPK	INT	Unique identifier for the login event
user_id	INT	Identifier of the user who logged in
login_at	TIMESTAMP	Timestamp when the user logged in

Your question is Average Session Duration Query. 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

Given a table of user login timestamps, write a query to calculate the average session duration, defining a session break as 30 minutes of inactivity at Twilio.

Output

Return one row with average_session_duration_minutes.
The result must be a numeric average across all users' sessions.

Schema

user_logins

Column	Type	Description
login_idPK	INT	Unique identifier for the login event
user_id	INT	Identifier of the user who logged in
login_at	TIMESTAMP	Timestamp when the user logged in

Your question is Average Session Duration Query. 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.