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". Free accounts get 10 free challenge solves, and this can be one of them.
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
Read the concept: WHERE vs HAVING
The trap to watch for: BETWEEN on timestamps
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.
Preparing for Capital One? How the CodeSignal data analyst assessment works.
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