SQL Quest › SQL Interview Questions › Aggregation & Grouping
The Ledger's First Week, Day by Day
A date window on a TIMESTAMP column is where careful people still lose a day. txn_at is a full ISO timestamp — 2026-03-10T18:42:11.930Z, not 2026-03-10. Ask for txn_at <= '2026-03-10' and every transaction after midnight on the 10th sorts as greater than that string and disappears. The safe form is a half-open window: >= start AND < the day after the end.
The ledger opens on 2026-03-04. Summarise its first seven days — 2026-03-04 through 2026-03-10 inclusive — one row per calendar day: day (date(txn_at)), txn_count, daily_spend (SUM of amount, rounded to 2 decimals) and avg_ticket (AVG of amount, rounded to 2 decimals). Seven rows, and if you get six you have just met the bug this question is about. Order by day ascending.
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: 2026-03-04 18 2011.18 111.73; 2026-03-05 31 4036.08 130.2; ...
Hint
date() strips the time part for the grouping key; the WHERE clause compares the raw ISO strings, which sort chronologically because the format is fixed-width.Concepts
SELECT WHERE GROUP BY Date Functions AVG
Practise the topic: SQL practice questions · GROUP BY exercises · Date function practice
In these company practice sets
Capital One · Plaid · Wise · 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