SQL Quest › SQL Interview Questions › Aggregation & Grouping
Spend by Day of Week
Card volume has a weekly shape and every issuer's staffing plan depends on it. strftime('%w', txn_at) turns a timestamp into a weekday code — a one-character string, '0' for Sunday through '6' for Saturday. There is no day-name function in SQLite, so the label comes from a CASE over that code.
Return dow (the '%w' code as it comes, a string), day_name (Sunday … Saturday), txn_count, total_spend (SUM of amount, rounded to 2 decimals) and avg_ticket (AVG of amount, rounded to 2 decimals). Seven rows. Order by dow ascending.
Read the answer before you move on: the busiest day and the biggest-basket day are not the same day.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
transactions
| txn_id | account_id | amount | txn_at | merchant_id | lat | lng | status |
|---|---|---|---|---|---|---|---|
| 1 | 149 | 151.84 | 2026-03-04T12:39:39.078Z | 24 | 40.342 | -74.4092 | completed |
| 2 | 21 | 10.72 | 2026-03-04T13:04:50.641Z | 7 | 52.9366 | 13.3229 | completed |
| 3 | 35 | 158.78 | 2026-03-04T13:27:15.528Z | 25 | 52.7397 | 13.3134 | completed |
Expected output: 0 Sunday 316 40860.8 129.31; 1 Monday 294 41105.72 139.82; ...
Hint
Concepts
SELECT GROUP BY CASE Date Functions strftime GROUP BY + CASE
Practise the topic: SQL practice questions · GROUP BY exercises · CASE WHEN practice · Date function practice
In these company practice sets
Capital One · Ramp · Bloomberg
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test