The activation query lies on day one: anti-joins and backfill in PostgreSQL

The most common churn metric is a ratio: usage over the last 7 days divided by usage over the previous 7. It is good at detecting decline. And it is blind to the worse case — the account that pays every month and has never used anything. This post is about the three traps in implementing that metric in PostgreSQL, and why its first result is almost always false.

The problem is not the division by zero

The intuitive explanation is that the ratio divides by zero. It does not. An account that never generated an event produces no rows at all in the events table — it never reaches the GROUP BY. There is no division to go wrong, because there is no row. And COALESCE will not save you: you cannot default a row that is absent.

-- Errado: o FROM parte de events. Uma conta que nunca gerou
-- evento nao produz nenhuma linha aqui, entao ela nao chega
-- no GROUP BY. Nao e uma razao 0/0 -- e uma conta ausente.
SELECT
  e.account_id,
  count(*) FILTER (
    WHERE e.created_at >= now() - interval '7 days'
  )::numeric
  / nullif(count(*) FILTER (
      WHERE e.created_at >= now() - interval '14 days'
        AND e.created_at <  now() - interval  '7 days'
    ), 0) AS razao_uso
FROM events e
GROUP BY e.account_id;

The defect is in the FROM, not in the arithmetic. As long as the query starts from events, its universe is "accounts that have already done something". To see the ones that never did, the FROM has to start from subscriptions — the table that knows who pays — and look for events from there.

Anti-join, not LEFT JOIN

With the FROM inverted, the question becomes "which subscriptions have no matching row in events". That is an anti-join against your largest table, and there are two popular ways to write it wrong. The first is NOT IN:

-- Pior ainda: se algum account_id retornado for NULL, o
-- predicado inteiro vira UNKNOWN e a query devolve ZERO linhas.
-- Nao da erro. So responde "nao ha contas inativas".
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
  AND s.account_id NOT IN (
    SELECT e.account_id FROM events e
    WHERE e.type IN ('report_created', 'integration_connected')
  );

If account_id is nullable and any row comes back NULL, the comparison becomes UNKNOWN for every account and the result is an empty set. The query does not fail, does not warn — it just answers that everything is fine.

The second is LEFT JOIN ... WHERE e.id IS NULL. This one works: the PostgreSQL planner recognizes the pattern and executes it as an anti-join. The problem is fragility, not performance. The day someone adds a filter on e to the WHERE instead of the ON, the LEFT JOIN silently becomes an INNER JOIN and the query starts discarding exactly the rows you were looking for. NOT EXISTS states the intent and has no such failure mode:

-- O FROM parte de subscriptions: toda conta que paga entra,
-- tenha evento ou nao. NOT EXISTS declara a intencao (anti-join)
-- e o predicado correlacionado fica onde pertence.
SELECT s.account_id, s.mrr_cents
FROM subscriptions s
WHERE s.status = 'active'
  AND NOT EXISTS (
    SELECT 1
    FROM events e
    WHERE e.account_id = s.account_id
      AND e.type IN ('report_created', 'integration_connected')
  );

-- Indice parcial: eventos relevantes sao uma fracao do total,
-- entao o indice fica pequeno e e exatamente o que o anti-join
-- sonda. CONCURRENTLY para nao travar a escrita em producao.
CREATE INDEX CONCURRENTLY idx_events_relevantes
  ON events (account_id)
  WHERE type IN ('report_created', 'integration_connected');

The backfill that does not exist

Here is the trap that makes the metric lie on day one. first_meaningful_event_at IS NULL has two meanings the column cannot tell apart: "this account never activated" and "we were not emitting that event when it signed up". If you started emitting report_created in March, every account older than March looks inactive — and the number comes out catastrophic because of a bug, not because of reality.

-- Guarda contra o falso positivo: so conta assinaturas que
-- comecaram depois que a instrumentacao existia. Antes disso,
-- ausencia de evento nao significa ausencia de uso.
SELECT sum(s.mrr_cents) AS mrr_sem_uso
FROM subscriptions s
WHERE s.status = 'active'
  AND s.started_at >= timestamp '2026-03-01'  -- instrumentacao
  AND NOT EXISTS (
    SELECT 1
    FROM events e
    WHERE e.account_id = s.account_id
      AND e.type IN ('report_created', 'integration_connected')
  );

There is no retroactive fix. An event that was not emitted cannot be reconstructed, and no query recovers data that was never written. What you can do is be explicit about the boundary: the metric only holds for subscriptions that started after instrumentation. The cut shrinks the sample, but the number starts meaning something.

Where the column lives

Running that anti-join on demand, in a dashboard that refreshes on every load, is expensive. The alternative is to maintain a derived column — first_meaningful_event_at on the account — written exactly once, when the first meaningful event arrives:

-- Write-once: o WHERE ... IS NULL torna a escrita idempotente
-- e segura sob concorrencia. Nao precisa ler antes, nao precisa
-- transacao, e o segundo evento nao sobrescreve o primeiro.
UPDATE accounts
   SET first_meaningful_event_at = $2
 WHERE id = $1
   AND first_meaningful_event_at IS NULL;

The WHERE ... IS NULL does the heavy lifting: it makes the write idempotent, removes the need for a prior read, and is safe under concurrency, because two simultaneous events race for the same row and only the first finds NULL. From then on the metric is a scan over accounts, never touching events. The cost is the usual one for derived columns: if the definition of "meaningful use" changes, you have to recompute.

What counts as meaningful use

The part no query solves is the definition. A login is not activation — it is someone signing in to rediscover that they do not know what to do there. Meaningful use is the action that produces the value the account paid for:

A report generated, not a report opened
An integration connected with data flowing, not a token saved
An invite accepted by a second user, not an invite sent
A record created by the customer, not the onboarding sample records

Choosing which events belong on that list is a product decision. Making them emit reliably, index well and compute without melting the database is an engineering one. The metric is only honest once both have been made — and made beforehand, not after.