Complex SQL With Windows | Dataford Interview Questions

Write a complex SQL query to join multiple tables and perform window functions on user activity logs at Roblox.

Return activity events from completed sessions belonging to active users. Include the requested sequence values and exclude events outside their session time range.

Output

One row per qualifying activity event.
Columns: user_id, username, session_id, session_started_at, event_time, event_type, event_value, session_number, and event_number.
Order by user, session start time, event time, and log ID.

Schema

users

Column	Type	Description
user_idPK	INT	Roblox user identifier
username	VARCHAR(100)	Display username
region	VARCHAR(20)	User's geographic region
account_status	VARCHAR(20)	Current account status

sessions

Column	Type	Description
session_idPK	INT	Unique gameplay session identifier
user_id	INT	User who started the session
started_at	TIMESTAMP	Session start time
ended_at	TIMESTAMP	Session end time
platform	VARCHAR(20)	Client platform
session_status	VARCHAR(20)	Session completion status

activity_logs

Column	Type	Description
log_idPK	INT	Unique activity event identifier
session_id	INT	Session associated with the event
event_time	TIMESTAMP	Time the activity occurred
event_type	VARCHAR(30)	Activity classification
event_value	DECIMAL(10,2)	Optional numeric value associated with the event

Your question is Complex SQL With Windows. Start with the requirements and the three 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