# Both Ways

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

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Meta

## Problem

We're reconciling daily net revenue for an online marketplace, where a positive amount is a purchase and a negative amount is a refund. Include every purchase dated between January 1 and April 30, plus every refund no matter when it happened, and give each day's net revenue.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 47 rows:

| transaction_date | net_revenue |
| --- | --- |
| 2026-01-07 | 1222.29 |
| 2026-01-08 | 858.6 |
| 2026-01-09 | 494.91 |
| 2026-01-10 | -45 |
| 2026-01-11 | 144.69 |

## Worked solution and explanation

### An asymmetric filter hiding inside a revenue report

Purchases and refunds sit in one column, told apart only by the sign of `total_amount`, and the date window clamps the purchases but not the refunds. Anyone can total a day's rows; the tell is whether you let a January purchase's May refund still count. Apply the window to both sides and you quietly erase every late refund, overstating each day's take.

> **Trick to Solving**
>
> The asymmetry is the whole problem: purchases must fall in Jan 1 to Apr 30, but refunds apply regardless of date. Read the two qualifiers apart: "every purchase dated between January 1 and April 30, plus every refund no matter when it happened."
> 
> 1. Select in-window purchases (positive amounts)
> 2. UNION ALL every refund (negative amounts) with no date restriction
> 3. Total by day

---

### Break down the requirements

#### Step 1: Select date-bounded purchases

Filter `total_amount > 0` and keep only rows where `transaction_date` falls inside the window.

#### Step 2: Select all refunds

Filter `total_amount < 0` with no date restriction at all. This is the branch candidates get wrong.

#### Step 3: Total by day

UNION ALL the two sets, then `GROUP BY transaction_date` with `SUM(total_amount)`. Because refunds are already negative, the sum is net revenue.

---

### The solution

**Asymmetric date filter for purchases vs refunds**

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

> **Cost Analysis**
>
> Two scans of 80M rows, each with a cheap sign predicate. An index on `(transaction_date)` prunes the purchase branch to the window, while the refund branch still needs a full pass. The UNION ALL output is small relative to the base table.

> **Interviewers Watch For**
>
> Whether the candidate spots that the date window applies to purchases only. Most apply the range to both branches, which silently drops every refund that lands outside it.

> **Common Pitfall**
>
> Bounding refunds by the window misses late refunds, such as a January purchase refunded in May. The prompt is explicit that refunds carry no date restriction.

---

## Common follow-up questions

- What if a transaction has `total_amount` = 0? _(It falls out of both branches. Tests whether zero is a purchase or a refund.)_
- How would you compute a running net revenue total? _(Add SUM(`net_revenue`) OVER (ORDER BY `transaction_date`) for cumulative net.)_
- How does this scale when refunds can trail purchases by months? _(Discusses partition pruning on `transaction_date` and whether the refund branch can be bounded at all.)_
- How would you break this out by product? _(Add `product_id` to the SELECT and GROUP BY to get a per-product daily ledger.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/both_ways)
- [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.