# Daily Net Revenue

> Net revenue, day by day. Refunds included.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

Product 1001 needs a daily revenue reconciliation between January 1 and April 30, 2025. Positive transaction amounts are purchases and negative values are refunds. Include all qualifying purchases in that date range, plus any refunds for the same product regardless of date.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests conditional date filtering with UNION logic. Purchases are date-bounded but refunds are not, which creates an asymmetric filter that trips up candidates who apply the date range uniformly.

> **Trick to Solving**
>
> The key asymmetry: purchases must fall in Jan 1 to Apr 30, but refunds for the same product apply regardless of date. Spot this by reading the qualifiers carefully: "qualifying purchases in that date range, plus any refunds for the same product regardless of date."
> 
> 1. Select purchases for product 1001 in the date range
> 2. UNION ALL refunds for product 1001 with no date restriction
> 3. Aggregate by day

---

### Break down the requirements

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

Filter `product_id = 1001`, `total_amount > 0`, and `transaction_date` in the specified range.

#### Step 2: Select all refunds for the product

Filter `product_id = 1001` and `total_amount < 0` with no date restriction.

#### Step 3: Aggregate by day

UNION ALL the two sets, then `GROUP BY transaction_date` with `SUM(total_amount)`.

---

### The solution

**Asymmetric date filter for purchases vs refunds**

```sql
WITH relevant AS (
    SELECT transaction_date, total_amount
    FROM transactions
    WHERE product_id = 1001
      AND 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 product_id = 1001
      AND 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 filtered by product_id. An index on `(product_id, transaction_date)` would make both scans efficient. The UNION ALL output is small for a single product.

> **Interviewers Watch For**
>
> Whether the candidate recognizes the asymmetric date filter. Most candidates apply the date range to both purchases and refunds, which silently drops refunds outside the window.

> **Common Pitfall**
>
> Applying the date filter to refunds would miss late refunds (e.g., a January purchase refunded in May). The prompt explicitly says refunds have no date restriction.

---

## Common follow-up questions

- What if a refund has total_amount = 0? _(It would be excluded by both conditions. Tests whether zero should be treated as a purchase or refund.)_
- How would you compute a running net revenue total? _(Add SUM(net_revenue) OVER (ORDER BY transaction_date) for cumulative.)_
- What if the product had millions of transactions? _(Discusses index optimization and whether to pre-filter by product.)_
- How would you extend this to all products? _(Add product_id to the GROUP BY and remove the product_id filter.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/daily_net_revenue)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.