FinOps: SELECT * on the orders mart

SQL data engineering interview problem. Difficulty: beginner. Pattern: Aggregation. About 15 minutes. Free to practice.

The operations team has a dashboard that shows how many orders came in each day and how much money they brought. The dashboard is slow and the cloud bill for it keeps growing. When someone looked at the query, they found it reads every column of the orders table and joins two big tables that it never uses.

Build a daily order summary for the ops dashboard. Return 1 row for each day with these columns: order_date (the date part of orders.created_at), order_count (how many orders were placed that day) and total_gmv (the sum of order_total, rounded to 2 decimal places). Leave out cancelled orders. Sort by order_date. The editor starts from a slow query that uses SELECT * and joins order_items and ecommerce_events. You pass only if the result is correct and your efficiency score is at least 70, so the query must also be cheap to run: only read what you need.

Requirements

  • Return 1 row for each day with order_date, order_count and total_gmv.
  • Do not count cancelled orders, and filter them out before you aggregate.
  • Do not use SELECT *. Name the columns you need.
  • Do not join tables that the answer does not use.
  • Sort by order_date.

Constraints

  • Only the orders table is needed to answer this question.
  • order_count is COUNT(*) per day and total_gmv is ROUND(SUM(order_total), 2) per day.
  • Return only these 3 columns, in this order: order_date, order_count, total_gmv.
  • Your query is also scored on efficiency, not only on the result.

Examples

Input: -- orders on 2026-08-01 order_id | order_status | order_total ORD-0032 | paid | 725.97 ORD-0115 | cancelled | 741.80 ORD-0148 | cancelled | 767.45 Output: order_date | order_count | total_gmv 2026-08-01 | 6 | 3943.48 Why this passes: Cancelled orders are excluded before COUNT and SUM. total_gmv sums order_total for the remaining rows that day.

Topics: finops, projection, aggregation.

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

beginner

FinOps: SELECT * on the orders mart

Production ticket: Daily order summary uses SELECT * with unused joins; FinOps wants named columns on orders only.

Build a daily order summary for the ops dashboard: - order_date, order_count, total_gmv - order_date from `orders.created_at` - exclude cancelled orders - ordered by order_date The editor starts from a bad query that SELECT * and joins `order_items` and `ecommerce_events`. Pass = correct output **and** efficiency score ≥ 70. Do not SELECT *, do not join unused tables, filter cancelled before aggregate.