Chapter 05 · 12 min · Basic
What you exclude, and why
You will be asked for March revenue. You will not be asked which orders count as March revenue, and that is the part of the question that actually has an answer worth defending.
Ten orders in this table. One cancelled, one refunded, two from an internal test account, and one real order forty times larger than any other. Depending on which of those you keep, March revenue is anywhere between RM 705 and RM 54,097 — a factor of seventy-six, with no dirty data anywhere and no wrong query.
This chapter is not about WHERE. It is about the three questions underneath it: which exclusions are definitional, which are judgment, and how you report a number so that the judgment travels with it.
The dataset
Ten March orders across five customers, one of whom is flagged is_internal. Statuses are completed, cancelled and refunded. One completed order from a real customer is RM 48,000 — deliberately not an error, so the decision about it stays a judgment call rather than a cleanup task.
Schema
| customer_id | int |
| name | text |
| is_internal | boolean |
| order_id | int |
| customer_id | int |
| order_date | date |
| status | text |
| amount_myr | numeric(10,2) |
Example data
There is no such thing as the unfiltered number
Sum the amount column and March revenue is RM 54,097 across ten orders, averaging RM 5,410 an order.
Every part of that sentence is defensible and all of it is useless. It includes an order that was cancelled, an order that was refunded, two orders placed by an internal test account, and one order forty times larger than any other.
The instinct is to call the filtered version "the real number" and this one "the raw number". That framing is wrong and it causes the argument you will have later. SUM(amount_myr) is not the number before exclusions — it is the number under the exclusion policy "include everything", which is a policy nobody would defend if you stated it out loud. You are not choosing between filtering and not filtering. You are choosing which filter, and the only real question is whether you can say what it is.
Ten orders, one of them very large. Will the average order value be anywhere near a typical order in this table?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. Both data-science tracks are the paid half — this one is the level-up: grain, reshaping, change over time, and whether the number really moved.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Three kinds of exclusion, three kinds of risk
- 03Report the ladder, not the final number
- 04The outlier that is not a mistake
- 05Write the exclusion down where the number lives
- 06Now decide it where the rows are not countable