Broken report: revenue excludes pending and refunded

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

Finance reports revenue every week. This week the revenue looked too low, and when they checked, orders that are pending or refunded were missing from the total. The contract that Finance signed says only cancelled orders should be left out.

The editor contains the revenue query that was shipped to production. It only counts orders where order_status = 'paid', but the contract says to count every order that is not cancelled. That means paid, pending and refunded orders should all be included. Fix the query so it returns 1 row with 1 column, total_gmv, which is the sum of order_total for all non-cancelled orders, rounded to 2 decimal places.

Requirements

  • Include orders whose status is paid, pending or refunded.
  • Leave out orders whose status is cancelled.
  • Return 1 row with 1 column called total_gmv.
  • Round the sum to 2 decimal places.

Constraints

  • The table is orders.
  • order_status can be paid, pending, refunded or cancelled.
  • Return only 1 column: total_gmv.

Examples

Input: order_id | order_status | order_total ORD-0098 | pending | 391.64 ORD-0069 | refunded | 842.26 ORD-0115 | cancelled | 741.80 Output: total_gmv 72167.54 Why this passes: Pending and refunded orders count toward total_gmv. Cancelled ORD-0115 is excluded. The buggy paid-only filter drops pending and refunded entirely.

Topics: debugging, filters, revenue.

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

beginner

Broken report: revenue excludes pending and refunded

Production ticket: The exec dashboard under-reports GMV because the job only counts paid orders.

The shipped revenue query filters `order_status = 'paid'`. The finance contract counts **all non-cancelled** orders (paid, pending, and refunded). Fix the SQL so: - total_gmv: 1 row, ROUND(SUM(order_total), 2) across eligible orders - cancelled orders are excluded The editor starts from the buggy query that shipped to production.