SQL Quest › SQL Interview Questions › Aggregation & Grouping

Monthly Spend Per Account

EasyFreeQuerying BasicsAggregation & Grouping

The first question on every card-analytics screen: how much does each cardholder spend per month? The ledger stores one row per transaction with an ISO timestamp in txn_at. Bucket it by calendar month with strftime('%Y-%m', txn_at) and sum the amounts.

Return account_id, month (formatted YYYY-MM), and monthly_spend (SUM of amount, rounded to 2 decimals). One row per account per month. Order by account_id ascending, then month 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: account_id=1, month=2026-03, monthly_spend=501.57 ...

Hint

GROUP BY account_id, strftime('%Y-%m', txn_at). Alias the strftime expression as month in the SELECT and you can reuse the alias in GROUP BY and ORDER BY in SQLite.

Concepts

SELECT GROUP BY SUM strftime GROUP BY + Date Functions

Practise the topic: SQL practice questions · GROUP BY exercises

Read the concept: How to find duplicates in SQL

In these company practice sets

Capital One · Ramp · Revolut · Wise · Goldman Sachs · 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.

Monthly Spend Per Account

Schema

Hint

Concepts

In these company practice sets

Related questions