Active User Revenue for April

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

Each row in transactions records a purchase's quantity and total_amount, and a purchase's revenue is the two multiplied together. Return the total revenue from April 2026 purchases made by users whose account in users is active.

Total revenue from active users in a single month

Tables

transactions

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

Strip the finance costume and this is a filtered sum whose filter lives in the wrong table. Everything you add up sits on transactions; the one fact that decides which rows count, whether the account is active, sits only on users. Anyone can write a SUM over April. What separates candidates is two quiet mistakes: reading 'quantity times amount' as two totals multiplied together, and skipping the join because the date filter feels like enough. Multiply the totals and the answer is orders of magnitude too large; skip the join and suspended, inactive and still-verifying accounts pad the reconciliation.

Build it in order

Step 1: Bring account_status onto each purchase

Inner-join transactions to users on user_id. Status is a property of the person, not the purchase, so there is nothing to filter on until the two tables meet. The inner join also drops any purchase whose user has no row in users: revenue nobody can vouch for stays out of a reconciliation.

Step 2: Keep only 'active', and only April

u.account_status = 'active' is an exact match; 'inactive', 'suspended' and 'pending_verification' are all out. The month test compares the year-month of transaction_date against '2026-04', pinning year and month together so April of an earlier year in the data never leaks in.

Step 3: Multiply first, then add

SUM(t.quantity * t.total_amount) computes each purchase's revenue and adds those. Sum-of-products and product-of-sums are different numbers, and only the first is revenue. There is no GROUP BY: the ask is one figure.

SELECT SUM(t.quantity * t.total_amount) AS total_revenue
FROM transactions t
INNER JOIN users u ON t.user_id = u.user_id
WHERE u.account_status = 'active'
  AND strftime('%Y-%m', t.transaction_date) = '2026-04'

Product of totals

SUM(quantity) * SUM(total_amount) pairs every unit from every purchase with every dollar from every other purchase. Even on a month with a handful of rows, the result is a cross-product no ledger will ever match.

Total of products

SUM(quantity * total_amount) keeps each purchase's quantity attached to its own amount before anything is added, so each row contributes exactly its own revenue.

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

Common follow-up questions

• Finance now wants the same total per `age_bucket`, and `age_bucket` is sometimes `NULL`. What changes, and where do those users' purchases go? (Moving from one figure to a grouped result, and deciding what a missing dimension value means.)
• How would you rewrite the month filter so an index or partition on `transaction_date` can still be used? (Sargable range predicates versus a function applied to the column.)
• A refund shows up as a row with a negative `total_amount`. Does your total handle it correctly, and what would you ask finance before deciding? (Whether the candidate inspects the data for signed values instead of assuming every amount is positive.)
• If `users` kept one row per status change instead of one row per user, what would happen to your join? (Fan-out from a dimension that is no longer unique on `user_id`.)