FinOps: dashboard GMV is scanning the lake

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 25 minutes. Part of the Pro drill bank.

The serving dashboard shows revenue (called GMV, gross merchandise value) for the last 7 days. It runs often, and the cloud bill depends on how much data each query reads. FinOps, the team that watches the cost, set a rule: the query must read as little as possible. A correct answer that reads too much will still fail.

From the orders table, build the daily GMV and its rolling 7-day average. Ignore cancelled orders. Return order_date (the calendar day of created_at), gmv (the sum of order_total for that day, rounded to 2 decimals) and gmv_7d_avg (the average of gmv over this day and the 6 days before it). Order by order_date. To pass, the numbers must be right and the query must also be efficient. Do not use a CROSS JOIN, do not use SELECT *, remove the cancelled orders before the aggregation, and only read the orders table. Joining the events or order items tables only reads data you do not need.

Requirements

  • Exclude orders with order_status = 'cancelled' before you group.
  • Group by the day of created_at and sum order_total.
  • Compute the 7-day average over the daily totals with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
  • Return order_date, gmv, gmv_7d_avg, ordered by order_date.
  • Do not use SELECT *, a CROSS JOIN, or other tables.

Constraints

  • gmv is ROUND(SUM(order_total), 2) for each calendar day.
  • The average on the first days uses fewer than 7 rows. Early days average over the days that exist.
  • Only the columns you need should be read from orders.

Examples

Input: -- orders created on 2026-08-01 order_id | order_status | order_total ORD-0032 | paid | 725.97 ORD-0098 | pending | 391.64 ORD-0115 | cancelled | 741.80 ORD-0148 | cancelled | 767.45 ORD-0069 | refunded | 842.26 Output: order_date | gmv | gmv_7d_avg 2026-08-01 | 3943.48 | 3943.48 Why this passes: Both cancelled orders (741.80, 767.45) are excluded from gmv. On the first day of data, the 7-day rolling window has only 1 row, so gmv_7d_avg equals gmv.

Topics: finops, EXPLAIN, projection.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

FinOps: dashboard GMV is scanning the lake

Production ticket: Same rolling 7-day GMV, but the ticket fails unless the plan stops cross-joining the clickstream.

The serving dashboard needs rolling 7-day GMV from `orders` (exclude cancelled). Output contract: - order_date, gmv, gmv_7d_avg - ordered by order_date Correct numbers are not enough. FinOps attached a bytes budget: **do not CROSS JOIN**, **do not SELECT ***, **filter cancelled before the aggregate**, and keep the simulated scan on the orders fact. Joining `ecommerce_events` / `order_items` is unused I/O. Pass = matching grain **and** efficiency score ≥ 70.