DB Olympics

Queries · q096 · Head of Data · week 1, night 3

Customer headcount by definition

“Do different reasonable definitions of 'how many customers we have' or 'how many are active' give meaningfully different headcounts?”

Manager · asked the questionRosa Van den BergHead of Data. Took over the analytics team last week. Before the team's numbers go to the board she wants to know how far they can be trusted.
Analyst · planned the reportYuki TanakaDetail-oriented: picks specific merchants, products or days and looks at them closely.
Engineer · wrote the SQLFelixJunior engineer who nests subqueries in FROM and WHERE (IN, EXISTS, correlated subqueries) rather than CTEs.

Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told

  1. A business event lands on the Head of Data's desk

    Week 1; shared with Tobias Lindqvist, Chief Financial Officer

    Before the board pack goes out, Data must certify the key metrics. Do revenue, orders, customers, refunds and payouts agree wherever they are recorded, and where they do not, why not and by how much?

    One of the 35 events we wrote for the role play; it shaped the manager's first note. How events arise · All the events

  2. Rosa Van den Berg writes to Yuki Tanaka

    The manager, at the start of week 1

    Subject: Certifying the key metrics before the board pack — what I need from you this week

    Hi Yuki,

    Tobias and I have to certify revenue, orders, customers, refunds and payouts before they go into the board pack. "Certify" means we're telling the board these numbers are reconcilable — so this week's job is to find out where the different places we record these figures agree, where they don't, and whether any gap is big enough to change the story we tell. If something doesn't reconcile, I need to know why and by how much, not just that it's "close enough." That feeds directly into the go/no-go Tobias and I give on the board pack, so please prioritize breadth across the five metrics over depth on any one of them — we can always dig deeper next week on whichever one looks shakiest.

    You only have a handful of report slots a night, so here's what matters most to me, roughly in priority order:

    1. If someone asks "what was our revenue last month," do we get the same answer regardless of which part of the business we ask? Where it diverges, how far apart are the answers, and is the gap explained by something sensible (timing, currency, returns) or does it look like a real inconsistency?
    2. Do our order counts and our revenue tell a consistent story — i.e., is there a clean, traceable link from an order to the money that was actually collected for it, or are there orders that seem to vanish or multiply along the way?
    3. Refunds and payouts: when money goes back to a customer, does it show up consistently as a reduction in revenue and in what we actually pay out to merchants? I want to know if refunds could be hiding or double-counted anywhere.
    4. Customers: can we count "how many customers we have" or "how many are active" in one consistent way, or do different parts of the data give meaningfully different headcounts?
    5. If you have a slot left: is there anything about how timestamps or currencies are handled that could quietly distort any of the above when we roll numbers up across time or across merchants?

    Please flag anything you find inconsistent together with your best read of why, and roughly how big the discrepancy is in practice — that's what Tobias and I need to decide whether we can certify as-is, certify with a caveat, or need to hold a number back.

    Thank you — I know this is a lot for one week. Let's talk before you spend your first slot tonight if you want to sanity-check the plan.

    Rosa

    A note from the Chief Financial Officer, Tobias Lindqvist

    Subject: What I need for the revenue/payout certification

    Hi Yuki,

    A few things from my side as you scope this week's work:

    1. Does our reported revenue tie out end to end? I want to know whether what we book as platform fees and processing fees matches what actually settles to us through payouts — and if there's a gap, roughly how large it is and whether it's timing (month-end cutoff) or something structural.
    2. Do merchant payouts reconcile to the gross volume and refunds we report? I'm particularly interested in whether net payouts consistently equal gross sales minus refunds minus our fees, or whether certain merchants/currencies show persistent drift.
    3. Are our B2B wholesalers' outstanding receivables consistent with what we'd show in the annual accounts? I want to understand how much is genuinely overdue versus just lagging normal payment terms, since that affects how we present them.

    If anything else jumps out while you're in there — especially around currency conversion timing or refund treatment — flag it even if it's outside these three.

    Thanks, Tobias

  3. Yuki Tanaka plans the night

    The analyst · week 1, night 3, request 3 of 3

    Night 2 gave three follow-ups worth running tonight. (1) The order-status breakdown showed "refunded" orders at a near-total 99.8% gap and "fulfilled" orders at a persistent 22% gap — I suspect both are partly a filter artifact: my captured-payment match only counted payments.status='captured', but a payment that was later refunded moves to status 'refunded'/'partially_refunded' and would be invisible to that filter even though money genuinely changed hands. I want to re-run with a status-inclusive filter to see how much of the gap is this artifact vs a real reconciliation hole, especially for 'fulfilled' orders where there's no reason to expect any gap at all. (2) The payment-linked payout/refund check got noisier, not cleaner, than the calendar-month version (swinging -16.6% to +17.7% with no consistent sign) — payout_items has a unique constraint on payment_id (one row per payment, ever), so a refund processed after a payment's payout has already settled has no slot in that payout to be reflected in; it would have to hit some later, differently-linked settlement, breaking the clean per-payout linkage I assumed. I want to directly test how often refunds are processed after their payment's payout already settled, since that would explain the noise and is itself a traceability gap worth flagging to Tobias. (3) Rosa's customer-count question (#4) hasn't been touched yet and we're entering the back half of the week, so I'm using tonight's third slot to get a first cut at how many "customers" we have under different plausible definitions, to see if that's a quick certify or another can of worms for night 4.

    Customer headcount by definition
    Question
    Do different reasonable definitions of 'how many customers we have' or 'how many are active' give meaningfully different headcounts?
    One row per
    customer count definition
    Columns
    • definition
    • user_count
    • pct_of_all_customers

  4. Felix writes the SQL

    The engineer · 1 dry run on the empty schema, then the submission

    Dry run 1 · STATUS: COMPILES (dry run on the empty schema, no data read)
    SELECT
  t.definition,
  t.user_count,
  round(t.user_count * 100.0 / tot.total_count, 1) as pct_of_all_customers
FROM (
  SELECT 'All customers (ever created)' as definition,
    (SELECT count(distinct u1.id) FROM users u1
       WHERE u1.is_staff = false AND u1.created_at < timestamp '2026-01-01') as user_count
  UNION ALL
  SELECT 'Status = active',
    (SELECT count(distinct u2.id) FROM users u2
       WHERE u2.is_staff = false AND u2.status = 'active')
  UNION ALL
  SELECT 'Placed at least one order, ever',
    (SELECT count(distinct u3.id) FROM users u3
       WHERE u3.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u3.id
                       AND o3.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Placed at least one order in trailing 12 months',
    (SELECT count(distinct u4.id) FROM users u4
       WHERE u4.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u4.id
                       AND o4.placed_at >= timestamp '2025-01-01'
                       AND o4.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Has an active or trialing subscription',
    (SELECT count(distinct u5.id) FROM users u5
       WHERE u5.is_staff = false
         AND EXISTS (SELECT 1 FROM subscriptions s5 WHERE s5.user_id = u5.id
                       AND s5.status IN ('active','trialing')))
  UNION ALL
  SELECT 'Logged in within last 90 days of data',
    (SELECT count(distinct u6.id) FROM users u6
       WHERE u6.is_staff = false
         AND u6.last_login_at >= timestamp '2025-10-03'
         AND u6.last_login_at < timestamp '2026-01-01')
) t
CROSS JOIN (
  SELECT count(distinct u0.id) as total_count
  FROM users u0
  WHERE u0.is_staff = false AND u0.created_at < timestamp '2026-01-01'
) tot
ORDER BY t.user_count DESC, t.definition

    “Query compiles and returns the expected columns. Submitting.”

    Submitted · the SQL of the last dry run, unchanged · Submitted to tonight's batch. Your work on this request is done.

    “Submitted. The report builds six customer-headcount definitions (all ever created, status=active, ever ordered, ordered in trailing 12 months, active/trialing subscription, logged in within 90 days) as one row each, with user_count and pct_of_all_customers computed against the "all customers ever created" denominator, ordered by headcount descending.”

  5. The report comes back overnight

    As Yuki Tanaka saw it, from the small dataset the agents worked on

    definition                               | user_count | pct_of_all_customers
-----------------------------------------+------------+---------------------
All customers (ever created)             |      11258 |                100.0
Status = active                          |      11183 |                 99.3
Placed at least one order, ever          |       9640 |                 85.6
Placed at least one order in trailing 12 |       6159 |                 54.7
Logged in within last 90 days of data    |       5396 |                 47.9
Has an active or trialing subscription   |        411 |                  3.7
(6 rows)

  6. What came of it

    The memo and the queries that followed; links found by title, so inferred

    Yuki Tanaka's memo to Rosa Van den Berg at the end of the week:

    • 4. "How many customers" has no single answer — pick two. (Customer headcount by definition) Range from 11,258 (all accounts) down to 411 (active/trialing subscription, 3.7% — expected, most merchants aren't subscription businesses). "Status=active" (99.3%) is not a usable activity signal. I'd certify two headline numbers: 9,640 ever-purchased and 6,159 trailing-12-month purchasers.

    Later queries that build on this report:

  7. It becomes benchmark query q096

    Changed only where the benchmark needs the same result on every system

    • numericsdocs/NUMERICS.md: rounding removed; results keep full precision
    • made explicita selected t.col gets AS col, its standard name made explicit (2×)
    • determinismthe standard leaves the place of NULLs in a sort to the implementation: NULLS LAST ascending, NULLS FIRST descending, as in the reference results (2×)
    The benchmark text, against the submitted SQL
    SELECT  t.definition,  t.user_count,  round(t.user_count * 100.0 / tot.total_count, 1) as pct_of_all_customers  t.definition AS definition,  t.user_count AS user_count,  t.user_count * 100.0 / tot.total_count as pct_of_all_customersFROM (  SELECT 'All customers (ever created)' as definition,    (SELECT count(distinct u1.id) FROM users u1       WHERE u1.is_staff = false AND u1.created_at < timestamp '2026-01-01') as user_count  UNION ALL  SELECT 'Status = active',    (SELECT count(distinct u2.id) FROM users u2       WHERE u2.is_staff = false AND u2.status = 'active')  UNION ALL  SELECT 'Placed at least one order, ever',    (SELECT count(distinct u3.id) FROM users u3       WHERE u3.is_staff = false         AND EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u3.id                       AND o3.placed_at < timestamp '2026-01-01'))  UNION ALL  SELECT 'Placed at least one order in trailing 12 months',    (SELECT count(distinct u4.id) FROM users u4       WHERE u4.is_staff = false         AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u4.id                       AND o4.placed_at >= timestamp '2025-01-01'                       AND o4.placed_at < timestamp '2026-01-01'))  UNION ALL  SELECT 'Has an active or trialing subscription',    (SELECT count(distinct u5.id) FROM users u5       WHERE u5.is_staff = false         AND EXISTS (SELECT 1 FROM subscriptions s5 WHERE s5.user_id = u5.id                       AND s5.status IN ('active','trialing')))  UNION ALL  SELECT 'Logged in within last 90 days of data',    (SELECT count(distinct u6.id) FROM users u6       WHERE u6.is_staff = false         AND u6.last_login_at >= timestamp '2025-10-03'         AND u6.last_login_at < timestamp '2026-01-01')) tCROSS JOIN (  SELECT count(distinct u0.id) as total_count  FROM users u0  WHERE u0.is_staff = false AND u0.created_at < timestamp '2026-01-01') totORDER BY t.user_count DESC, t.definitionORDER BY t.user_count DESC NULLS FIRST, t.definition NULLS LAST

    As each system received it; highlighted, the parts spelled for that system

    SELECT
  t.definition AS definition,
  t.user_count AS user_count,
  t.user_count * 100.0 / tot.total_count as pct_of_all_customers
FROM (
  SELECT 'All customers (ever created)' as definition,
    (SELECT count(distinct u1.id) FROM users u1
       WHERE u1.is_staff = false AND u1.created_at < timestamp '2026-01-01') as user_count
  UNION ALL
  SELECT 'Status = active',
    (SELECT count(distinct u2.id) FROM users u2
       WHERE u2.is_staff = false AND u2.status = 'active')
  UNION ALL
  SELECT 'Placed at least one order, ever',
    (SELECT count(distinct u3.id) FROM users u3
       WHERE u3.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u3.id
                       AND o3.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Placed at least one order in trailing 12 months',
    (SELECT count(distinct u4.id) FROM users u4
       WHERE u4.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u4.id
                       AND o4.placed_at >= timestamp '2025-01-01'
                       AND o4.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Has an active or trialing subscription',
    (SELECT count(distinct u5.id) FROM users u5
       WHERE u5.is_staff = false
         AND EXISTS (SELECT 1 FROM subscriptions s5 WHERE s5.user_id = u5.id
                       AND s5.status IN ('active','trialing')))
  UNION ALL
  SELECT 'Logged in within last 90 days of data',
    (SELECT count(distinct u6.id) FROM users u6
       WHERE u6.is_staff = false
         AND u6.last_login_at >= timestamp '2025-10-03'
         AND u6.last_login_at < timestamp '2026-01-01')
) t
CROSS JOIN (
  SELECT count(distinct u0.id) as total_count
  FROM users u0
  WHERE u0.is_staff = false AND u0.created_at < timestamp '2026-01-01'
) tot
ORDER BY t.user_count DESC NULLS FIRST, t.definition NULLS LAST
    100 ms1 sHyper351 msDuckDB398 msGizmoSQL350 msclickhouse_brew394 msFirebolt631 msDataFusion390 msClickHouse590 ms

    Scale factor 1000, c8a.24xlarge, after the last load. q096 on the start page, with every configuration.

  8. Everything, as recorded

    The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted

    Felix's whole session, 7 turns
    • The request, as Felix received it
      Report request from Yuki Tanaka (analyst for the Head of Data):

Title: Customer headcount by definition
Question: Do different reasonable definitions of 'how many customers we have' or 'how many are active' give meaningfully different headcounts?
Grain: customer count definition
Columns:
- definition
- user_count
- pct_of_all_customers
Details: Scope: all tenants combined, non-staff users only (users.is_staff = false). As-of date for all 'current state' columns: the data cutoff 2025-12-31. Rows (definitions), each counting distinct users.id: (1) 'All customers (ever created)' - all non-staff users with created_at <= 2025-12-31; this is the denominator for pct_of_all_customers. (2) 'Status = active' - users.status = 'active'. (3) 'Placed at least one order, ever' - distinct users.id appearing in orders (any status) with placed_at <= 2025-12-31. (4) 'Placed at least one order in trailing 12 months' - distinct users.id with an order placed_at between 2025-01-01 and 2025-12-31. (5) 'Has an active or trialing subscription' - distinct users.id with a subscriptions row where status in ('active','trialing'). (6) 'Logged in within last 90 days of data' - users.last_login_at >= 2025-10-03. pct_of_all_customers = user_count / (count from definition 1) * 100, rounded to 1 decimal. Tables: users, orders, subscriptions. Order by user_count descending.

Check your query with the warehouse's dry_run tool, then hand it in with submit.
    • Felix calls dry_run
      SELECT
  t.definition,
  t.user_count,
  round(t.user_count * 100.0 / tot.total_count, 1) as pct_of_all_customers
FROM (
  SELECT 'All customers (ever created)' as definition,
    (SELECT count(distinct u1.id) FROM users u1
       WHERE u1.is_staff = false AND u1.created_at < timestamp '2026-01-01') as user_count
  UNION ALL
  SELECT 'Status = active',
    (SELECT count(distinct u2.id) FROM users u2
       WHERE u2.is_staff = false AND u2.status = 'active')
  UNION ALL
  SELECT 'Placed at least one order, ever',
    (SELECT count(distinct u3.id) FROM users u3
       WHERE u3.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u3.id
                       AND o3.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Placed at least one order in trailing 12 months',
    (SELECT count(distinct u4.id) FROM users u4
       WHERE u4.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u4.id
                       AND o4.placed_at >= timestamp '2025-01-01'
                       AND o4.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Has an active or trialing subscription',
    (SELECT count(distinct u5.id) FROM users u5
       WHERE u5.is_staff = false
         AND EXISTS (SELECT 1 FROM subscriptions s5 WHERE s5.user_id = u5.id
                       AND s5.status IN ('active','trialing')))
  UNION ALL
  SELECT 'Logged in within last 90 days of data',
    (SELECT count(distinct u6.id) FROM users u6
       WHERE u6.is_staff = false
         AND u6.last_login_at >= timestamp '2025-10-03'
         AND u6.last_login_at < timestamp '2026-01-01')
) t
CROSS JOIN (
  SELECT count(distinct u0.id) as total_count
  FROM users u0
  WHERE u0.is_staff = false AND u0.created_at < timestamp '2026-01-01'
) tot
ORDER BY t.user_count DESC, t.definition
    • The warehouse answers
      STATUS: COMPILES (dry run on the empty schema, no data read)
COLUMNS: definition, user_count, pct_of_all_customers
    • FelixQuery compiles and returns the expected columns. Submitting.
    • Felix calls submit
      SELECT
  t.definition,
  t.user_count,
  round(t.user_count * 100.0 / tot.total_count, 1) as pct_of_all_customers
FROM (
  SELECT 'All customers (ever created)' as definition,
    (SELECT count(distinct u1.id) FROM users u1
       WHERE u1.is_staff = false AND u1.created_at < timestamp '2026-01-01') as user_count
  UNION ALL
  SELECT 'Status = active',
    (SELECT count(distinct u2.id) FROM users u2
       WHERE u2.is_staff = false AND u2.status = 'active')
  UNION ALL
  SELECT 'Placed at least one order, ever',
    (SELECT count(distinct u3.id) FROM users u3
       WHERE u3.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o3 WHERE o3.user_id = u3.id
                       AND o3.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Placed at least one order in trailing 12 months',
    (SELECT count(distinct u4.id) FROM users u4
       WHERE u4.is_staff = false
         AND EXISTS (SELECT 1 FROM orders o4 WHERE o4.user_id = u4.id
                       AND o4.placed_at >= timestamp '2025-01-01'
                       AND o4.placed_at < timestamp '2026-01-01'))
  UNION ALL
  SELECT 'Has an active or trialing subscription',
    (SELECT count(distinct u5.id) FROM users u5
       WHERE u5.is_staff = false
         AND EXISTS (SELECT 1 FROM subscriptions s5 WHERE s5.user_id = u5.id
                       AND s5.status IN ('active','trialing')))
  UNION ALL
  SELECT 'Logged in within last 90 days of data',
    (SELECT count(distinct u6.id) FROM users u6
       WHERE u6.is_staff = false
         AND u6.last_login_at >= timestamp '2025-10-03'
         AND u6.last_login_at < timestamp '2026-01-01')
) t
CROSS JOIN (
  SELECT count(distinct u0.id) as total_count
  FROM users u0
  WHERE u0.is_staff = false AND u0.created_at < timestamp '2026-01-01'
) tot
ORDER BY t.user_count DESC, t.definition
    • The warehouse answers
      Submitted to tonight's batch. Your work on this request is done.
    • FelixSubmitted. The report builds six customer-headcount definitions (all ever created, status=active, ever ordered, ordered in trailing 12 months, active/trialing subscription, logged in within 90 days) as one row each, with `user_count` and `pct_of_all_customers` computed against the "all customers ever created" denominator, ordered by headcount descending.

    Not shown here: 240 KB of context the agent was given, its instructions and the schema documentation; it is in the raw transcript.

    Felix's transcript (266 KB) · Yuki Tanaka's transcript (954 KB) · Rosa Van den Berg's transcript (262 KB) · the night's plan (6 KB) · the night's reports (3 KB) · the week's memo (3 KB)