Metric definition

A precise statement of what is counted: measure, population, condition, period and exclusions. Two people with the same definition and data get the same number.

Formula[measure] of [population], [condition], [period], excluding [exclusions]

Try it · 30 seconds · from Atterna’s practice

Which definition lets two analysts get the same number from the same data?

Preparing the practice…

How we work it through

1
Write the sentence before the query and send it to whoever asked.
2
Name the exclusions out loud: tests, staff, cancelled.
3
Only then write SQL — the query follows the sentence.

On the practice dataset

The query we run on Meridian, Atterna’s practice marketplace, and what it returns.

SELECT COUNT(*) AS registered,
  COUNT(*) FILTER (WHERE purchases >= 1) AS paid_once,
  COUNT(*) FILTER (WHERE purchases >= 2) AS paid_twice
FROM (SELECT b.buyer_id, COUNT(DISTINCT o.checkout_id) AS purchases
      FROM meridian.buyers b
      LEFT JOIN meridian.orders o ON o.buyer_id = b.buyer_id
                                 AND o.status = 'paid'
      GROUP BY b.buyer_id);

Result7,000 · 3,652 · 2,020 — three honest answers to “how many customers?”

The common trap

Starting with the query. Write the definition first: “[measure] of [population], [condition], [period], excluding [exclusions]”.

ExampleOne database of 7,136 accounts gives 2,141, 3,670 and 2,339 “customers” for three different definitions.

When an AI answers

An AI assistant

“We have 7,000 customers.”

What it didn’t know: Nobody told it whether a customer must have paid, or when.
Ask back: “Define customer in one sentence: who, which action, which period, which exclusions.”

Paste a real AI answer into the checker — it writes these questions for you →