Find Duplicate Sessions in Clickstream | Dataford Interview Questions

Your question is Find Duplicate Sessions in Clickstream. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

ScienceDirect clickstream data can contain duplicate sessions caused by retries, browser behavior, or instrumentation issues. Write a PostgreSQL query to identify sessions that appear duplicated for the same user, device, and browser.

Treat a later session as a likely duplicate when it has the same user_id, device_type, and browser, and starts within five minutes of an earlier session. Use a self-join and conditional aggregation.

Requirements

Return the earlier session, user name, start time, number of later matching sessions, number of exact duplicates, and matching session IDs.
Count an exact duplicate when the matching session has the same start timestamp and landing page.
Include only sessions with at least one later match, ordered by the earlier session start time.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Elsevier platform user identifier
user_name	VARCHAR(100)	Display name for the user

clickstream_sessions

Column	Type	Description
session_idPK	INT	Unique clickstream session identifier
user_id	INT	User associated with the session
session_start	TIMESTAMP	Timestamp when the session began
device_type	VARCHAR(30)	Device category used for the session
browser	VARCHAR(40)	Browser reported by the client
landing_page	VARCHAR(200)	First page visited during the session