25. Hours from order to settlement
Asked in screens shaped like: Stripe, Adyen, PayPal
Treasury wants average hours between ordered_at and paid_at, by payment method. One payment row per paid order in this warehouse. Return method and avg_hours rounded to 2 decimals, slowest method first.
Requirements
- Columns: method, avg_hours
- avg_hours is ROUND(AVG(hours from ordered_at to paid_at), 2)
- Join payments to orders on order_id
- Order by avg_hours descending, then method
Expected output
method, avg_hours
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
payments
~182 rows
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGER— joins orders
- methodTEXT— card, ach, wallet
- amountREAL
- paid_atTEXT
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
(JULIANDAY(paid_at) - JULIANDAY(ordered_at)) * 24 is hours in SQLite.
Hint 2
GROUP BY method after the join. Do not average timestamps as strings.
SELECT p.method,
ROUND(AVG((JULIANDAY(p.paid_at) - JULIANDAY(o.ordered_at)) * 24), 2) AS avg_hours
FROM payments p
JOIN orders o ON o.order_id = p.order_id
GROUP BY p.method
ORDER BY avg_hours DESC, p.method;Why it works
Lag is a timestamp difference, then an average. Grouping by method after the join keeps grain honest — averaging ordered_at as text would sort, not measure hours.
Run your query to see the result set.
Submit to run the checks.
Why it works
Lag is a timestamp difference, then an average. Grouping by method after the join keeps grain honest — averaging ordered_at as text would sort, not measure hours.
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