SQL interview question: time between consecutive purchases

MediumCompare with previous

Problem

For each acquisition channel, find the gaps between July–August 2026 purchases and the same buyer’s next purchase. Return their count, average length in days, and the share of gaps up to 7 days.

Formulas

gap = next purchase time − purchase time, in days (seconds / 86,400)
share_within_7d — share of gaps ≤ 7 × 24 h

Example

Input: users

user_id	acq_channel
1	ads
2	referral

Input: orders

order_id	user_id	created_at	status	Row note
101	1	2026-08-10 12:00	delivered
102	1	2026-09-01 01:15	delivered	← September — next after 101
103	2	2026-08-20 15:00	delivered
104	2	2026-08-27 15:00	delivered	← exactly 7 × 24 h — counts
105	2	2026-09-10 11:00	delivered	← September — next after 104

Order 102: September — next after 101
Order 104: exactly 7 × 24 h — counts
Order 105: September — next after 104

Output

acq_channel	gaps	avg_gap_days	share_within_7d
ads	1	21.55	0
referral	2	10.42	0.5

ads: Aug 10 to Sep 1 — 21.55 days. referral: 7 and 13.83 days — average 10.42; only the first is within 7 days, share 0.5.

Notes

A purchase with no next purchase is skipped.

Solve it free

Your query runs on PostgreSQL in your browser. No sign-up needed.

Skills it tests

Window functions
Date differences

SQL interview question: time between consecutive purchases

Problem

Example

Notes

Skills it tests

More interview questions