SQL Session Reconstruction with Dirty Timestamps

Given a raw event log of user interactions with missing and duplicate timestamps, write a query to clean the data and reconstruct clean user sessions for Slack.

Treat received_at as the timestamp when event_ts is missing. Remove exact duplicate interactions, retain deterministic ordering for events sharing a timestamp, and start a new session when the gap between consecutive events for a user is greater than 30 minutes.

Output

One row per user session with user_id, session_number, session_start, session_end, event_count, and event_types
Include events from all users, with event types listed in chronological order
Order by user_id, then session_number

Schema

raw_user_events

Column	Type	Description
event_idPK	INT	Unique identifier for the raw event record
user_id	INT	Slack user identifier
event_type	VARCHAR(40)	Type of user interaction
event_ts	TIMESTAMP	Client-reported interaction timestamp, which may be missing
received_at	TIMESTAMP	Timestamp when the event was received by the logging service
payload	VARCHAR(255)	Compact event payload used to identify exact duplicate interactions

Your question is SQL Session Reconstruction with Dirty Timestamps. 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 raw event log of user interactions with missing and duplicate timestamps, write a query to clean the data and reconstruct clean user sessions for Slack.

Output

One row per user session with user_id, session_number, session_start, session_end, event_count, and event_types
Include events from all users, with event types listed in chronological order
Order by user_id, then session_number

Schema

raw_user_events

Column	Type	Description
event_idPK	INT	Unique identifier for the raw event record
user_id	INT	Slack user identifier
event_type	VARCHAR(40)	Type of user interaction
event_ts	TIMESTAMP	Client-reported interaction timestamp, which may be missing
received_at	TIMESTAMP	Timestamp when the event was received by the logging service
payload	VARCHAR(255)	Compact event payload used to identify exact duplicate interactions

Your question is SQL Session Reconstruction with Dirty Timestamps. 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.