A right number and a useful number are not the same

The query was correct. The answer was wrong.

This is the lesson that separates people who can write SQL from people you can trust with a decision. Your query can be flawless (correct joins, correct filters, no NULL traps) and still produce a number that misleads everyone in the room.

Here is the shape of it, four ways.

The definition nobody agreed on

“How many active users do we have?” You write the query. You return 41,200.

But nobody said what active means. Signed in during the last 30 days? Took any action at all? Has a live subscription? Opened the app once, ever, and never deleted the account? Those four numbers might be 41,200 and 12,400 and 6,900 and 380,000. All four are correct. Three of them will get someone fired.

The fix is not technical. Before the query, write the sentence: ”one row of my result is one account that performed at least one action between 1 and 31 March, in the account’s own time zone.” Send that sentence to whoever asked, before you send them a number.

The denominator that moved

A team reports that conversion rate rose from 2.1% to 3.4% after a redesign. The numerator query is correct. The denominator query is correct. What happened is that an analytics script broke on mobile, so a third of the visits stopped being recorded. Fewer visits, same purchases, higher rate.

Whenever a ratio moves, look at the top and the bottom separately. A ratio hides which half changed, and it is very often the half you were not thinking about.

Survivorship

A subscription business computes churn as: of the customers currently in the customers table, what share have status 'cancelled'. It returns 4%.

The problem is that customers who cancelled and were then purged, or archived, or moved to another table, are not in the denominator at all. You are measuring churn among the people who did not leave. Adding a date filter does not help; the missing people are missing before any filter runs.

The general form: whenever your population is defined by having survived, you cannot use it to measure survival.

The average that lands where nobody is

Mean order value on an Indian marketplace: ₹4,800. Ninety percent of orders are between ₹300 and ₹900. A handful of bulk business orders are ₹200,000 each. The mean is arithmetically perfect and describes no customer who has ever existed.

Report the median. Report the 90th percentile next to it. If the median is ₹620 and the mean is ₹4,800, that gap is itself the finding, and it is more interesting than either number alone.

Time zones and late data, briefly

“Yesterday’s revenue” computed in UTC for a business in Asia/Kolkata moves the boundary by five and a half hours, which reliably shifts the evening rush into the wrong day. And today’s number is always low, because payments settle, webhooks retry and refunds land days later. A dashboard whose last bar always dips has taught its whole company that things are getting worse.

What to do about all of this

Say the sentence first. “One row of my result is ____, counted over ____, between ____ and ____ in ____ time zone, excluding ____.” If any blank is unfillable, go and ask.

Check the number against something you already know. Last month’s figure. A total from the finance system. Ten rows counted by hand. A number with nothing to compare it against is a number nobody can challenge, including you.

Try to break your own filter. Change a condition so the result should move. If it does not move, your filter is not doing anything, and you have found that out for free instead of in a meeting.

Ship the query with the number. Always. A figure in a message with no query behind it cannot be argued with, and things that cannot be argued with are how organisations end up confidently wrong for a year.