19. Payment method mix by day
Asked in screens shaped like: Stripe, PayPal, Adyen
Turn payment methods from rows into columns so each day is a single row with card, ach, and wallet totals. Warehouses have a PIVOT clause; plain SQL does it with conditional aggregation.
Requirements
- Columns: paid_day, card, ach, wallet
- paid_day is the date part of paid_at
- Each method column sums that method’s amount for the day, 0 when absent
- All money columns rounded to 2 decimals, ordered by paid_day
Expected output
paid_day, card, ach, wallet
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
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
One CASE expression per output column.
Hint 2
ELSE 0 keeps the sum numeric on days a method is missing.
Hint 3
Group by DATE(paid_at) only — the method must not be in the GROUP BY.
SELECT DATE(paid_at) AS paid_day,
ROUND(SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END), 2) AS card,
ROUND(SUM(CASE WHEN method = 'ach' THEN amount ELSE 0 END), 2) AS ach,
ROUND(SUM(CASE WHEN method = 'wallet' THEN amount ELSE 0 END), 2) AS wallet
FROM payments
GROUP BY DATE(paid_at)
ORDER BY paid_day;Why it works
Conditional aggregation is portable pivoting. The cost is that every output column is hard-coded, so a new payment method means editing the query — which is the honest trade-off to state in an interview.
Run your query to see the result set.
Submit to run the checks.
Why it works
Conditional aggregation is portable pivoting. The cost is that every output column is hard-coded, so a new payment method means editing the query — which is the honest trade-off to state in an interview.
Pro problem
Medium 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