24. Second order within seven days
Asked in screens shaped like: Shopify, DoorDash, Uber
Retention wants customers whose second completed order landed within 7 days of their first completed order. Return customer_id and the days between those two orders, smallest customer_id first.
Requirements
- Columns: customer_id, days_between
- Only completed orders
- days_between is the day gap between 1st and 2nd completed order
- Keep rows where days_between <= 7
- Order by customer_id
Expected output
customer_id, days_between
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
ROW_NUMBER completed orders per customer by ordered_at.
Hint 2
Self-join row 1 to row 2, then JULIANDAY difference.
WITH ranked AS (
SELECT customer_id, ordered_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS rn
FROM orders
WHERE status = 'completed'
)
SELECT a.customer_id,
CAST(ROUND((JULIANDAY(b.ordered_at) - JULIANDAY(a.ordered_at))) AS INTEGER) AS days_between
FROM ranked a
JOIN ranked b ON b.customer_id = a.customer_id AND a.rn = 1 AND b.rn = 2
WHERE (JULIANDAY(b.ordered_at) - JULIANDAY(a.ordered_at)) <= 7
ORDER BY a.customer_id;Why it works
The second order is row_number = 2, not “any later order.” Compare only those two timestamps. A 7-day window is a date difference, not a count of orders.
Run your query to see the result set.
Submit to run the checks.
Why it works
The second order is row_number = 2, not “any later order.” Compare only those two timestamps. A 7-day window is a date difference, not a count of orders.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems