Follow the Money

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

mid

Asked in

Truist

Problem

The account analytics team is closing out this year's books and wants a read on who is moving the most money through the platform. Add up each account's transaction amounts for 2026 and list them from the biggest total down.

Every dollar leaves a trail. Find the accounts moving the most of it.

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this is really asking

Under the banking costume this is a one-table rollup: collapse a year of transactions to one row per account and sum the money. It looks trivial, and that is exactly why interviewers use it as a screen. Two small decisions separate a clean answer from a wrong one: scoping the date filter to 2026 on a date column without breaking on string formats, and summing amounts per account instead of counting rows. Get the grain wrong and you report transaction counts dressed up as dollars.

Break down the requirements

Step 1: Scope to this year

The ask is 2026 only, and the table spans several years. strftime('%Y', transaction_date) = '2026' pulls the year out of each date and compares it as text, which is exact and avoids off-by-one boundary mistakes around January 1.

Step 2: Roll up to the account grain

GROUP BY user_id collapses many transactions per account into one row. This is the unit the team cares about: the account, not the individual transaction.

Step 3: Sum the money, then sort

SUM(total_amount) is the metric, not COUNT(*). Name the column total_spent so the output reads as dollars, then ORDER BY it descending so the heaviest accounts sit at the top. A user_id tiebreak keeps the ordering stable when two accounts tie.

The solution

SELECT user_id,
       SUM(total_amount) AS total_spent
FROM transactions
WHERE strftime('%Y', transaction_date) = '2026'
GROUP BY user_id
ORDER BY total_spent DESC, user_id

Common follow-up questions

• Show only the top 10 accounts by yearly volume. (Tests LIMIT after the sort and whether they reason about ties at the cutoff.)
• Add a column for each account's average transaction size alongside the total. (Probes mixing AVG(`total_amount`) with SUM in the same grouped query.)
• Make this run incrementally each night instead of scanning the whole year. (Pushes toward the sargable date range and partition pruning on `transaction_date`.)