Rank Within Sessions | Dataford Interview Questions

Rank transactions within customer sessions using a window function, given events that can arrive out of order with lagging timestamps. Asked in the SQL round stage. Candidate used ROW_NUMBER() partitioned by customer_id ordered by event_time; interviewer drilled into session boundaries and replay idempotency.

Output

Return one row per valid, deduplicated transaction within a session.
Include customer_id, session_id, event_id, event_time, transaction_amount, and transaction_rank.
Rank earlier event_time values first within each customer session, excluding events outside session boundaries and placing tied timestamps in event_id order.
Order results by customer_id, session_id, event_time, and event_id.

Schema

sessions

Column	Type	Description
session_idPK	INT	Unique customer session identifier
customer_id	INT	Customer associated with the session
session_start	TIMESTAMP	Inclusive session start timestamp
session_end	TIMESTAMP	Inclusive session end timestamp

events

Column	Type	Description
event_idPK	INT	Unique physical event record identifier
session_id	INT	Referenced customer session
event_type	VARCHAR(30)	Type of event
event_time	TIMESTAMP	Business timestamp when the event occurred
ingested_at	TIMESTAMP	Timestamp when the event arrived in the platform
replay_key	VARCHAR(50)	Logical event key used for replay deduplication
amount	DECIMAL(10,2)	Transaction amount

Your question is Rank Within Sessions. Start with the requirements and the two 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