# The Transaction Breakdown > Multiple time windows. One query. The business wants all of it at once. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 · Asked in: DoorDash ## Problem Execs keep asking whether transaction volume is actually speeding up, so finance froze its dashboard tile at the 2026-09-17 snapshot date and measures every horizon back from that day, never from whenever the query happens to run. From `transactions`, return one row with the transaction count over the last 30 days, the last 180 days, and all time. ## 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 1 of 1 rows: | last_30 | last_180 | all_time | | --- | --- | --- | | 16 | 81 | 200 | ## Worked solution and explanation ### What this really is This is three overlapping date filters folded into one row, dressed up as an exec dashboard tile. Each count needs its own predicate measured back from the same frozen date, `2026-09-17`, and a single `WHERE` can only carry one of them. Anyone can count rows. What separates candidates is moving the predicate inside the aggregate. There are two traps. Anchoring to the wall clock or to `MAX(transaction_date)` makes the tile drift every time it re-runs. Slicing the history into exclusive buckets undercounts `last_180` by every row already assigned to the 30-day slice. #### Step 1: Read the horizons as cumulative A sale from last week belongs in all three numbers. That rules out any shape that puts each transaction in exactly one bucket, so a `GROUP BY` over a bucket label is wrong before you write it. #### Step 2: Pin the as-of date Every horizon is measured back from `2026-09-17`, the date the tile is frozen at. It is not today and not the newest `transaction_date`. The latest row sits a few days before the snapshot, and either alternative shifts both windows. #### Step 3: Give each column its own predicate `SUM(CASE WHEN transaction_date >= DATE('2026-09-17', '-30 days') THEN 1 ELSE 0 END)` counts only the rows inside the 30-day horizon while the scan still sees every row. The `>=` keeps a sale dated exactly on the boundary day inside the window. #### Step 4: Let `all_time` count everything `COUNT(*)` needs no predicate, and with no `WHERE` it sees every row. There is no `GROUP BY` either, so the whole table collapses into the single row the tile shows. ### The solution **TIERED SNAPSHOT** ```sql SELECT SUM(CASE WHEN transaction_date >= DATE('2026-09-17', '-30 days') THEN 1 ELSE 0 END) AS last_30, SUM(CASE WHEN transaction_date >= DATE('2026-09-17', '-180 days') THEN 1 ELSE 0 END) AS last_180, COUNT(*) AS all_time FROM transactions; ``` > **The filter lives in the column, not the query** > > A `WHERE` filters rows for every column at once. When each column needs a different slice of the same rows, the condition has to move inside the aggregate: **one `CASE` per horizon**. **Exclusive buckets** Label each row '30d', '180d' or 'older', then `GROUP BY` the label. A recent sale lands only in '30d', so the 180-day number loses the whole last month, and the answer comes back as three rows instead of one. **Cumulative horizons** Each column tests its own `>=` predicate against the same as-of date. A recent sale passes both tests, so it counts in `last_30`, `last_180` and `all_time`. > **`CURRENT_DATE` makes the tile drift** > > Swapping the literal for `CURRENT_DATE` or `MAX(transaction_date)` looks more general, but it gives a different answer whenever the anchor moves. Finance froze the tile so that two runs agree, and the query has to honor that. > **Ask what 'now' means before writing** > > Strong candidates ask whether the horizons run from today, from the newest row, or from a fixed snapshot. Then they suggest parameterizing it as `:as_of_date`, so a backfill reuses the same query. > **One pass, and `all_time` sets the cost** > > `all_time` touches every partition, so this is one full scan. The two `CASE` expressions add only cheap per-row arithmetic. Three separate queries would read the recent partitions three times for the same three numbers. ## Common follow-up questions - How would you add a `prior_30` column covering 31 to 60 days before the as-of date, for a period-over-period comparison? _(Probes a closed window on `transaction_date` that does not overlap `last_30`.)_ - What changes if `transactions` is partitioned by month instead of day? _(Tests partition-pruning intuition and whether you see that boundary partitions are scanned in full.)_ - How would you make this query reproducible for a backfill against historical as-of dates? _(Checks whether you parameterize the as-of date instead of hardcoding it.)_ - Could you return these three metrics per `product_id` instead of one row total? _(Probes keeping the conditional sums while adding a grouping key, and what happens to rows with a NULL `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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.