Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

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

Requirements

  • Output is ordered by order_date, with one row per day that has at least one non-cancelled order.

Constraints

  • order_status = 'cancelled' orders are excluded before aggregation, not filtered out of the final result.
  • gmv is ROUND(SUM(order_total), 2) per calendar day derived from created_at.
  • gmv_7d_avg uses ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, so early days average over fewer than 7 rows.
  • ecommerce_events.event_type: page_view | add_to_cart | purchase | bounce.

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 one 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.