Both Ways

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Meta

Problem

Our daily net revenue for the second quarter of 2026 won't tie out, partly because refunds in the transactions table land on whatever day they happen: a purchase is a positive total_amount and a refund a negative one. Take every purchase dated April 1 through June 30, 2026, plus every refund no matter its date, and give each day's net revenue in date order.

Money flows in and money flows back. Reconcile the day.

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

Two date scopes hiding in one revenue report

This is two row sets with different date scopes dressed up as a daily ledger. Purchases and refunds share total_amount, told apart only by sign, and the April-to-June window clamps the purchases but not the refunds. Anyone can total a day's rows; what separates candidates is whether the January refund still counts. Put the window on every row and the '-45.0' day on '2026-01-10' disappears, so the quarter reads richer than the money that actually moved.

Building it

Step 1: Select the quarter's purchases

Keep rows where total_amount > 0 and transaction_date sits between '2026-04-01' and '2026-06-30'. The strict > 0 is doing work: the voided sale on '2026-06-06' carries '0.0' and is neither a purchase nor a refund.

Step 2: Select every refund

Keep rows where total_amount < 0 with no date condition at all. This is the branch candidates get wrong, and on this data it is the only way the January refund reaches the result.

Step 3: Stack the sets, then total by day

UNION ALL keeps every row from both sides; a plain UNION would collapse two identical purchases on the same day into one. Then GROUP BY transaction_date with SUM(total_amount): refunds are already negative, so the sum is the net. Return the days earliest first, the way the ledger reads.

WITH ledger_rows AS (
    SELECT transaction_date, total_amount
    FROM transactions
    WHERE total_amount > 0
      AND transaction_date >= '2026-04-01'
      AND transaction_date <= '2026-06-30'
    UNION ALL
    SELECT transaction_date, total_amount
    FROM transactions
    WHERE total_amount < 0
)
SELECT transaction_date, SUM(total_amount) AS net_revenue
FROM ledger_rows
GROUP BY transaction_date
ORDER BY transaction_date

SELECT transaction_date, SUM(total_amount) AS net_revenue
FROM transactions
WHERE transaction_date BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY transaction_date
ORDER BY transaction_date

Window on every row

A WHERE transaction_date BETWEEN ... over the whole table loses the '-45.0' refund day and keeps the '0.0' voided sale, so the result gains a June 6 day that should not exist.

Window on purchases only

The window sits in the purchase branch beside total_amount > 0; the refund branch carries only total_amount < 0, so refunds count whatever their date and voided sales never enter.

Common follow-up questions

• How would you add a running net revenue total down the ledger? (A `SUM(net_revenue) OVER (ORDER BY transaction_date)` over the daily totals.)
• How does this scale when refunds can trail purchases by months? (Partition pruning on `transaction_date` and whether the refund branch can be bounded at all.)
• What if a refund should only count when its original purchase fell in the quarter? (The rule changes from a sign test to a link back to the purchase, which needs an order reference this table does not have.)
• How would you break this out by product? (Add `product_id` to both branches and the `GROUP BY`, and decide what a `NULL` `product_id` means.)