# The Transaction Breakdown

> Multiple time windows. One query. The business wants all of it at once.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

Produce a single-row snapshot showing transaction counts at three time horizons: last 30 days, last 180 days, and all-time. One row, three numbers, used to illustrate acceleration trends on the exec dashboard.

## Worked solution and explanation

### Why this problem exists in real interviews

Exec dashboards want one row with multiple horizons, and the interviewer is checking whether you reach for conditional aggregation instead of three subqueries `UNION`-ed together. The second signal: do you ask about whether 'now' should be a runtime literal or a pinned snapshot date? Dashboards re-run; non-deterministic `now()` drifts results.

---

### Break down the requirements

#### Step 1: One row, three metrics

Output shape is a single row with `last_30`, `last_180`, `all_time`. No `GROUP BY`, no buckets to pivot. That tells you: aggregate the whole table once and let `CASE` decide what counts where.

#### Step 2: Pick the right aggregate

`SUM(CASE WHEN ... THEN 1 ELSE 0 END)` over `transaction_date`. `COUNT(CASE WHEN ...)` works too but `SUM(1/0)` is the convention interviewers expect. `all_time` is just `COUNT(*)`.

#### Step 3: Pin the boundary

`last_30` means `transaction_date >= DATE('now','-30 days')`. Same shape for `-180`. Say out loud whether the window is inclusive on both ends and whether future-dated rows can appear.

#### Step 4: Constrain the scan

If the interviewer asks about cost, add `WHERE transaction_date >= DATE('now','-180 days')` for the tiered metrics and compute `all_time` from a separate scalar subquery. Otherwise leave it as one pass.

---

### The solution

**TIERED SNAPSHOT**

```sql
SELECT
  SUM(CASE WHEN transaction_date >= DATE('now', '-30 days')  THEN 1 ELSE 0 END) AS last_30,
  SUM(CASE WHEN transaction_date >= DATE('now', '-180 days') THEN 1 ELSE 0 END) AS last_180,
  COUNT(*) AS all_time
FROM transactions;
```

> **Cost Analysis**
>
> `all_time` forces a full scan of all 80M rows regardless of partitioning. The `CASE` expressions are cheap CPU on top of that single pass. If you split `all_time` into its own scalar subquery and add `WHERE transaction_date >= DATE('now','-180 days')` to the tiered query, you get partition pruning for two of the three metrics.

> **Interviewers Watch For**
>
> Before writing, ask: is `now` real-time or an end-of-day snapshot? Dashboards almost always want a pinned reference date so the same query reruns deterministically. Mention parameterizing it as `:as_of_date` rather than hardcoding `'now'`.

> **Common Pitfall**
>
> Writing three separate queries and stitching them in the application layer, or worse, three `UNION ALL` rows that the dashboard then pivots. Both miss the point: a single row of metrics is one `SELECT` with conditional aggregation. No `GROUP BY` is the tell.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you add a `prior_30` bucket covering 31 to 60 days ago for a week-over-week comparison? _(Probes whether you can express a closed `BETWEEN DATE('now','-60 days') AND DATE('now','-31 days')` window without overlapping `last_30`.)_
- What changes if `transactions` is partitioned by month instead of day? _(Tests partition-pruning intuition and whether you understand boundary partitions get fully scanned.)_
- How would you make this query reproducible for a backfill against historical reference dates? _(Checks parameterization habits and the instinct to replace `'now'` with a bound variable.)_
- Could you return these three metrics per `product_id` instead of one row total? _(Probes the jump from a single-row aggregate to conditional aggregation under a `GROUP BY product_id`.)_

## Related

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