SQL Quest › SQL Interview Questions › Aggregation & Grouping

How Long Has Each Card Been Active?

MediumFreeQuerying BasicsAggregation & GroupingDate Functions

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". 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: 164 2026-04-07T03:32:53.326Z 2026-04-28T15:40:13.311Z 8 847.43 21.5; ...

Hint

GROUP BY account_id with MIN(txn_at), MAX(txn_at), COUNT(*), SUM(amount) — and ROUND(julianday(MAX(txn_at)) - julianday(MIN(txn_at)), 1) for the span. Nesting an aggregate inside julianday() is fine; it is evaluated after the grouping.

Concepts

SELECT GROUP BY Aggregation Date Functions JULIANDAY GROUP BY + Date Functions

Practise the topic: SQL practice questions · GROUP BY exercises · Date function practice

Read the concept: WHERE vs HAVING

In these company practice sets

Capital One · Wise

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.

How Long Has Each Card Been Active?

Schema

Hint

Concepts

In these company practice sets

Related questions