Use a WHERE filter with NOT IN to exclude lifecycle states that don't represent revenue. Single COUNT to settle the 'how many real orders do we have' question that splits revenue from gross-orders reporting.
The CFO and I are arguing about an order count. Marketing's slide says 100 orders YTD, finance's says less. The discrepancy is `voided` and `refunded` rows — they shouldn't count toward 'real' orders for the dashboard. Give me a single number: count of orders in `ecom_orders` that are NOT voided AND NOT refunded. Everything else (paid / partially_paid / pending / authorized / partially_refunded) stays in for now. We'll re-litigate `pending` next week.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | FK → ecom_customers |
| order_number | TEXT | |
| financial_status | TEXT | |
| fulfillment_status | TEXT | |
| total_price | REAL | |
| subtotal | REAL | |
| tax | REAL | |
| shipping | REAL | |
| discount_total | REAL | |
| channel | TEXT | |
| created_date | TEXT | |
| processed_date | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
`stage NOT IN ('voided', 'refunded')` is the filter — both have to be excluded at once.
`SELECT COUNT(*) FROM ecom_orders WHERE financial_status NOT IN ('voided', 'refunded')` gets you there. One row, one column.
Note the question deliberately keeps `partially_refunded` IN the count — the order partially shipped, the customer paid, the partial-refund is a different line item conceptually. The CFO will revisit if the partial-refund total grows.