Purchases on Consecutive Days | Dataford Interview Questions

The dv01 platform team wants to identify users who return on consecutive calendar days to make completed purchases. Write a PostgreSQL query that detects these repeat-purchase patterns from transaction timestamps.

Requirements

Consider only purchases with status = 'completed' and non-null user_id values.
Treat multiple completed purchases on the same calendar day as one purchase day.
Use a window function to identify adjacent purchase dates for each user.
Return users with at least one pair of consecutive days, including the number of consecutive-day pairs and the earliest and latest dates involved.
Sort by pair count descending, then user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
username	VARCHAR(100)	User display name

purchases

Column	Type	Description
purchase_idPK	INT	Unique purchase identifier
user_id	INT	User associated with the purchase
purchased_at	TIMESTAMP	Timestamp when the purchase occurred
status	VARCHAR(20)	Purchase processing status
channel	VARCHAR(30)	Purchase origin channel

Your question is Purchases on Consecutive Days. 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