# Closing the Books

> April is done. Finance wants the number, and it isn't the one printed on the invoice line.

Canonical URL: <https://datadriven.io/problems/closing_the_books>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Finance is closing April 2026 and needs a single revenue figure for the month. Each transaction's revenue is its `quantity` multiplied by the `total_amount` line total, so roll that up across every April 2026 transaction into one number.

## Worked solution and explanation

### What this really is

This is a one-table filtered total wearing a two-table costume. You are handed a customers table right next to the transactions, and the reflex on a finance question is to wire them together. Resist it. Nothing about a monthly revenue figure depends on who the customer is or what country they live in, so the customers table is pure decoy. The two things that actually decide the number are hiding in plain sight: which rows count (April only) and what revenue even means on a single row.

> **The two-column trap**
>
> The seductive wrong answer is SUM(total_amount). It runs, it returns one clean number, and it is quietly wrong. Revenue per line is quantity times total_amount, not total_amount alone. A row with quantity 4 and a 50.40 line contributes 201.60, not 50.40. Sum only the amount column and you undercount every multi-unit sale in the month, then hand Finance a figure that will not tie out to the ledger.

> **Multiply first, then total**
>
> The aggregate is over an expression, not a column: SUM(quantity * total_amount). The engine evaluates the product per row and adds the products. Do the multiplication inside the SUM, never SUM(quantity) * SUM(total_amount), which cross-multiplies unrelated rows and is meaningless.

#### Step 1: Fence the month before you total anything

Scope to April of the target year first. strftime('%Y-%m', transaction_date) = '2026-04' turns each date into a 'YYYY-MM' string and keeps only the April rows. Comparing a formatted month string is cleaner than a pair of BETWEEN boundaries you have to get exactly right, and it sidesteps any time-of-day component on the column.

#### Step 2: Total the per-row product

With the month fenced off, SUM(quantity * total_amount) collapses the surviving rows into the one figure Finance asked for. Alias it total_revenue so the single output column says exactly what it is.

**April revenue, one number**

```sql
SELECT SUM(t.quantity * t.total_amount) AS total_revenue
FROM transactions t
WHERE strftime('%Y-%m', t.transaction_date) = '2026-04'
```

*One table, one filter, one product-aggregate. No join earns its keep here.*

> **What the interviewer is watching**
>
> Two tells separate the seniors. First, do you reach for a join that the question never needed, or do you notice the customers table is bait and leave it out. Second, do you read total_amount as a per-line total that still has to be scaled by quantity. A candidate who writes the tight single-table product-aggregate without prompting has read the schema, not just the sentence.

> **Cheap by design**
>
> This is a single sequential scan of transactions with a filter and a running total: no join, no grouping, no sort, constant memory. On a large table the only lever is the WHERE, so in production you would keep transaction_date indexed or the table partitioned by month so April is a partition prune instead of a full scan.

## Common follow-up questions

- Now break it out by month instead of just April, one revenue row per calendar month, most recent first. _(Tests moving from a scalar filter to a grouped aggregate over the formatted month.)_
- Finance now wants April revenue split by customer country. How does the customers table come back into play? _(Tests recognizing when a join is actually load-bearing versus decoration, and joining on the right key.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/closing_the_books)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.