Caso Abierto Español

Field manual · SQL traps

Survivorship bias lives in the JOIN

Joining a history table against today's catalog measures who survived, not what actually happened: the ones that disappeared are exactly the ones carrying the bad news.

Where it shows up

Historical studies over living catalogs: asset universes, active customers, products in range. More generally, any "how did X do" where X might no longer exist.

Example

Wrong: only what's still listed today

SELECT h.date, avg(h.return) AS avg_return
FROM history h
JOIN universe u ON u.symbol = h.symbol AND u.active
GROUP BY h.date;
-- symbols that went bankrupt or got delisted aren't in "universe.active",
-- so their bad track record never enters the average

Right: the universe as it was BACK THEN

SELECT h.date, avg(h.return) AS avg_return
FROM history h
JOIN universe u
  ON u.symbol = h.symbol
 AND h.date >= u.added
 AND (u.removed IS NULL OR h.date <= u.removed)
GROUP BY h.date;

The "active" filter describes today's catalog, not the date of each row in the history table. The correct condition is temporal: a row counts if that symbol was part of the universe on that row's date — use the added/removed field (or its equivalent) to reconstruct the universe as it was back then, not as it looks after the worst cases have already disappeared.