The Transaction Breakdown

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

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.

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

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

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

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;

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.

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`.)