SQL Quest › SQL Interview Questions › Aggregation & Grouping
How Long Has Each Card Been Active?
First and last per group, and the span between them. MIN(txn_at) and MAX(txn_at) give the first and last transaction of every account in one pass — they work on a text column because the ISO format is fixed-width, so lexical order and chronological order are the same thing here. That is a property of the format, not a rule about strings.
The span is not: subtracting two timestamps as text gives nothing useful. Convert with julianday(), which returns a day number, and subtract those.
Return account_id, first_txn_at, last_txn_at, txn_count, total_spend (rounded to 2 decimals) and active_days (julianday(MAX) − julianday(MIN), rounded to 1 decimal). Show the 20 shortest spans — cards used in a burst and then quiet. Order by active_days ascending, then account_id 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: 164 2026-04-07T03:32:53.326Z 2026-04-28T15:40:13.311Z 8 847.43 21.5; ...
Hint
Concepts
SELECT GROUP BY Aggregation Date Functions JULIANDAY GROUP BY + Date Functions
Practise the topic: SQL practice questions · GROUP BY exercises · Date function practice
In these company practice sets
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