SQL: Consecutive Rejections Detection | Dataford Interview Questions

Your question is SQL: Consecutive Rejections Detection. 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

Instacart monitors shopper order decisions to identify repeated rejections that may indicate delivery friction or account issues. Write a PostgreSQL query that identifies every rejection immediately following another rejection by the same shopper.

Requirements

Join order decision events to shopper account data and retain events even when a shopper profile is unavailable.
Use ROW_NUMBER() to assign each shopper's events a chronological sequence, breaking timestamp ties with event_id.
Use LAG() to retrieve the previous decision for each shopper.
Return only rejected events whose immediately preceding decision by that shopper was also rejected. Include the shopper, event details, sequence number, and previous decision.

Schema

shopper_accounts

Column	Type	Description
shopper_idPK	INT	Unique shopper identifier
shopper_name	VARCHAR(100)	Shopper display name
market	VARCHAR(50)	Instacart market associated with the shopper

shopper_order_events

Column	Type	Description
event_idPK	INT	Unique order decision event identifier
shopper_id	INT	Shopper associated with the event
order_id	INT	Instacart order identifier
event_time	TIMESTAMP	Timestamp when the decision was recorded
decision	VARCHAR(20)	Shopper decision, accepted, rejected, or not recorded