SQL Detect Duplicate Clickstream Events | Dataford Interview Questions

Write a query to detect duplicate events in a clickstream log and return only the earliest timestamp for each unique session.

Treat rows with the same non-null session_id and event_name as duplicate events. Return sessions containing at least one duplicate event group, with the earliest timestamp across all events in each affected session.

Output

One row per affected session, with columns session_id and earliest_timestamp
Exclude null session IDs and duplicate groups with null event names
Order by session_id ascending

Schema

clickstream_events

Column	Type	Description
event_idPK	INT	Unique identifier for the clickstream event
session_id	VARCHAR(50)	Identifier for the user session
event_name	VARCHAR(100)	Name of the clickstream event
event_timestamp	TIMESTAMP	Timestamp when the event occurred

Your question is SQL Detect Duplicate Clickstream Events. 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

Write a query to detect duplicate events in a clickstream log and return only the earliest timestamp for each unique session.

Output

One row per affected session, with columns session_id and earliest_timestamp
Exclude null session IDs and duplicate groups with null event names
Order by session_id ascending

Schema

clickstream_events

Column	Type	Description
event_idPK	INT	Unique identifier for the clickstream event
session_id	VARCHAR(50)	Identifier for the user session
event_name	VARCHAR(100)	Name of the clickstream event
event_timestamp	TIMESTAMP	Timestamp when the event occurred

Your question is SQL Detect Duplicate Clickstream Events. 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.