Closing the Books

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

L3

Problem

Produce a single revenue figure for April 2026 from transactions, the number finance needs to close that month. Despite its name, total_amount is recorded per unit, so a sale brings in its quantity times that amount.

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

Tables

transactions

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

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

Expected output

Worked solution and explanation

What this really is

This is a sum of per-row products wearing a month-close costume, with a decoy table on the side. Nothing about one month's revenue depends on who the customer is, so customers is bait: leave it alone. Everyone gets the April filter. What separates candidates is where the multiplication happens. total_amount is a per-unit amount, so revenue only exists after each row's quantity scales it, and that product has to be formed row by row before anything is totaled. Skip the scaling and you undercount every multi-unit sale; multiply two totals instead and you inflate the month many times over.

Step 1: Fence the month before you total anything

strftime('%Y-%m', t.transaction_date) = '2026-04' turns each date into a 'YYYY-MM' string and keeps only the April rows. One equality on the formatted month is harder to get wrong than a pair of boundaries, and it keeps working if the column ever gains a time of day.

Step 2: Total the per-row product

With the month fenced, SUM(t.quantity * t.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.

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

Product of totals

SUM(quantity) * SUM(total_amount) pairs every row's units with every other row's amount. Two April sales, 4 units at 50.40 and 1 unit at 144.69, become 5 times 195.09, about 975.

Total of products

SUM(quantity * total_amount) multiplies inside each row first: 201.60 plus 144.69 is 346.29, the money those two sales actually brought in.

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 single filter to a grouped aggregate over the formatted month.)
• Finance now wants April revenue split by customer country. How does `customers` come back into play? (Tests recognizing when a join is actually load-bearing, joining on the right key, and noticing that some `customer_id` values repeat.)