Your question is Rank Within Sessions. 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.
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.
customer_id, session_id, event_id, event_time, transaction_amount, and transaction_rank.event_time values first within each customer session, excluding events outside session boundaries and placing tied timestamps in event_id order.customer_id, session_id, event_time, and event_id.| 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 |
| 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 |