Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Approach
I would model the customer segment as a slowly changing dimension, meaning a history table with valid_from and valid_to timestamps. Then I would do an as-of join, which matches each order to the dimension row that was valid when the order happened, not the current row.

SELECT
  DATE_TRUNC('month', o.order_ts) AS month,
  d.segment,
  SUM(o.revenue) AS revenue
FROM orders o
JOIN dim_customer_history d
  ON o.customer_id = d.customer_id
 AND o.order_ts >= d.valid_from
 AND o.order_ts <  d.valid_to
GROUP BY 1, 2;

Why this is auditable

• The join is driven by the order timestamp, so reclassifying a customer today does not rewrite history.
• I would keep immutable raw orders, the dimension history, and the monthly mart as separate layers.
• I would store the load batch or snapshot date so finance can reproduce the exact closing report.

Late data handling
If an order is backfilled after month close, I would rerun the affected month from raw facts, but only against the dimension history that was effective at the order time. That keeps the numbers consistent and explainable during reconciliation.

Direct answer: Writing the retention common table expression (CTE, a named WITH-clause subquery) and window-function pipeline is the easy 20%. At hundreds of millions of users, running it from raw events every night is the part that doesn't survive contact with production: the fix is to pre-aggregate raw events into a compact per-user-week activity table, partition that table (and the final matrix) by cohort or activity week so a write only ever touches the weeks it affects, and materialize the 12x12 retention matrix as a small table refreshed incrementally rather than recomputed from scratch. The two-sequential-event cohort assignment (signup, then a later onboarding-completed) and late-arriving backfill are really the same design problem in miniature: both require the pipeline to know precisely which slice of the matrix a given late-arriving event can possibly affect, so it only recomputes that slice.

Structured elaboration

Why nightly-from-raw-events doesn't scale: a retention matrix is a slow-moving key performance indicator, or KPI (it changes by a handful of new signups and a week's worth of new activity each night), but a from-scratch pipeline pays for scanning the entire multi-hundred-million-row, multi-week history every single run, more than 99% of which is unchanged from the previous night. The fix is incremental processing on top of a pre-aggregated, partitioned base table, not a faster version of the same full scan.

Performance strategy, in order of impact:

1. Pre-aggregate first. Collapse raw events (many rows per user per day) into user_week_activity(user_id, activity_week) once. This is the single biggest cardinality reduction: bounded by users x weeks, not users x events.
2. Partition by the dimension that changes. New activity this week affects every past cohort's retention row simultaneously (a user from a 10-week-old cohort being active this week updates that cohort's week-10 column), so partition/cluster user_week_activity by activity_week, and only append this week's slice. The matrix update for a given cohort_week + week_offset cell then follows directly from who was active in the newly-appended activity week and what their cohort_week was, which only requires touching users active that week, not the full user base.
3. Materialize the matrix as its own small table. 12 cohorts x 12 offsets is at most ~144 rows; refresh it with a MERGE/upsert against the incremental user_week_activity delta, and let dashboards read the small table, never the raw events.
4. Reserve sampling for exploration only. Approximate counts (e.g. HyperLogLog-style distinct estimators, a family of algorithms that trade a small, bounded error for counting distinct users in a fraction of the memory an exact count would need) are fine for an analyst poking at trends, but a nightly authoritative retention number is exactly the kind of metric (often reported externally or to leadership) where "approximately right" silently erodes trust if it doesn't reconcile with billing or engagement counts elsewhere.

Two-sequential-event cohort assignment (signup, then onboarding-completed): a cohort here isn't a single timestamp, it's the resolution of two ordered events for the same user. A LATERAL join expresses this cleanly:

WITH signups AS (
  SELECT user_id, MIN(event_time) AS signup_time
  FROM events WHERE event_type = 'signup'
  GROUP BY user_id
)
SELECT s.user_id, s.signup_time, ob.onboarding_time,
       DATE_TRUNC('week', ob.onboarding_time) AS cohort_week
FROM signups s
JOIN LATERAL (
  SELECT MIN(e.event_time) AS onboarding_time
  FROM events e
  WHERE e.user_id = s.user_id
    AND e.event_type = 'onboarding_completed'
    AND e.event_time >= s.signup_time
) ob ON ob.onboarding_time IS NOT NULL
ORDER BY s.user_id;

The ON ob.onboarding_time IS NOT NULL turns this into an inner-join-like filter: a user who signed up but never completed onboarding gets no cohort assignment at all, which is correct (they can't be placed on a retention matrix keyed by a week they never reached). A plain window-function alternative (e.g. a filtered MIN() OVER, or LEAD with a type filter) works too; LATERAL makes the "find the qualifying later event" condition explicit and lets an index on (user_id, event_type, event_time) serve it directly. This is a meaningfully different, stricter rule than a simpler daily first-touch cohort assignment (cohort_week = the week of a user's very first event of any kind, with no second qualifying event required); first-touch assignment never has an "unassigned" user, while the two-sequential-event rule always will, for anyone who never reaches step two, and that population needs to be tracked and reported on its own, not silently folded into "week 0 retention."

Late-arriving events and backfill without a full recompute: the failure mode to avoid is treating "backfill" as "rerun the whole pipeline." Instead, track which cohort_week partitions a batch of newly-arrived events actually touches, and MERGE only those:

• A late activity event for an existing cohort only dirties one (cohort_week, week_offset) cell: the user's own cohort_week and the offset implied by the late event's own week.
• A late onboarding_completed event is more dangerous, because it can retroactively change which cohort a user belongs to (a user with no prior cohort assignment now gets one, or one assigned to a wrong/placeholder cohort now moves). The dirty set for that user is the union of their old cohort_week (if any) and their new one; recompute both, not just the partition the late event's own timestamp falls in.
• A per-batch "touched cohort_weeks" list (derived from DATE_TRUNC('week', <the relevant event time>) on just the newly-arrived rows) is cheap to compute and is exactly what should drive the MERGE's WHERE/target-partition scope.

Worked example (executed in DuckDB)

Three events for two users: user 1 signs up 2026-01-01, completes onboarding 2026-01-03; user 2 signs up and completes onboarding both on 2026-01-02; user 3 signs up 2026-01-05 but never completes onboarding. Running the LATERAL cohort query above returns exactly two rows (users 1 and 2, both assigned cohort_week = 2025-12-29, the Monday-starting week containing their onboarding completion), and correctly omits user 3. A late-arriving onboarding event for user 2 at 2026-01-02 20:00 resolves to DATE_TRUNC('week', ...) = 2025-12-29, giving exactly the one dirty partition that a backfill job would need to re-MERGE, not the whole table.

Trade-offs & pitfalls

• Partitioning only by cohort_week (and not also by activity_week) is a common half-measure: it makes backfilling a mis-assigned cohort cheap, but doesn't help the every-night append of new activity, which is naturally keyed by activity_week instead.
• A recursive common table expression (recursive CTE, one that refers to itself to build a result iteratively; Postgres requires the RECURSIVE keyword explicitly, SQL Server does not) can generate the 0..11 week-offset series inline instead of a stored numbers/calendar table, but a small reusable calendar table is usually the better production choice: it's index-friendly and shared across every query that needs a week series, where a recursive CTE regenerates the same series from scratch each time it's used.
• Forgetting to widen the dirty-partition set to include a user's previous cohort_week when a late onboarding event reassigns them is the single most common backfill bug in this pattern: it leaves a stale, too-high retention count sitting in the old cohort's row.

Direct answer: Only a real temporary table survives past the one statement that defines it. A common table expression (CTE, a named, WITH-clause subquery) and a derived-table subquery are both parsed, planned, and executed as part of a single SQL statement; once that statement finishes, the name is gone and referencing it in a second, separate statement is an undefined-identifier error, transaction or no transaction. A temporary table is a real (if session-scoped) table object: CREATE TEMP TABLE persists across as many later statements as you want in the same session, and by default survives a transaction commit too, because its default lifetime policy is ON COMMIT PRESERVE ROWS.

Structured elaboration

Why a CTE and a subquery are identical on this specific axis: both are query-text constructs, not stored objects. A CTE gives a subquery a name so it can be referenced more than once within the same statement, and may or may not be materialized (engine- and version-dependent), but "materialized" here means "computed once, this statement's execution," not "persisted as a database object." Neither one creates anything that exists a moment after the statement completes. This is why the general readability trade-off between a CTE and an inline derived-table subquery (which is more readable, when nesting gets deep) is a separate question from the one asked here: on scope and persistence, they behave exactly alike.

Why a temp table is different: CREATE TEMPORARY TABLE (or the shorthand CREATE TEMP TABLE) creates an actual table, visible to every subsequent statement in the same database session, with its own storage and (optionally) its own indexes. Two separate statements that both need the same intermediate result can each just query it by name, the same as any other table. Postgres additionally lets you choose what happens to a temp table at commit via an ON COMMIT clause: PRESERVE ROWS (the default: nothing special happens), DELETE ROWS (an automatic TRUNCATE at every commit), or DROP (the table itself is dropped at commit, useful for a genuinely transaction-scoped scratch table). Left at the default, a temp table is the one construct of the three that reliably answers "yes" to both parts of the question: it survives across separate statements in a session, and it survives a transaction boundary.

Worked example (executed in DuckDB)

Statement 1 defines and uses a CTE in one call: WITH doubled AS (SELECT id, val * 2 AS v2 FROM t) SELECT * FROM doubled, which runs fine and returns three rows. Statement 2, a completely separate call that references doubled again with no CTE definition attached, fails immediately: Catalog Error: Table with name doubled does not exist. That's the CTE's statement-scoped lifetime, made concrete. By contrast, CREATE TEMP TABLE doubled_tmp AS SELECT id, val * 2 AS v2 FROM t run as statement 3, followed by SELECT SUM(v2) FROM doubled_tmp as a fully separate statement 4, succeeds and returns the correct aggregate (120), confirming the temp table persisted across the statement boundary that killed the CTE reference outright.

Trade-offs & pitfalls

• Don't reach for a temp table just to "reuse" an intermediate result inside a single statement; a CTE (or even a repeated subquery) already does that without the overhead of creating and later cleaning up a physical table object.
• A temp table needs explicit lifecycle management: an index you might want on it, and eventually a drop (or reliance on session end / ON COMMIT DROP) to avoid accumulating scratch tables in a long-lived session or connection-pooled application.
• "Survives a transaction boundary" is specifically about the table persisting; whether the rows in it survive depends on the ON COMMIT option chosen at creation, so "temp tables persist across commits" is only true under the default (PRESERVE ROWS), not universally.
• This whole comparison is Postgres-flavored; the mechanics of temporary tables (global temp tables, session variables, or engine-specific equivalents) vary enough across SQL Server, MySQL, and Oracle that the general shape (statement-scoped CTE/subquery vs. session-scoped temp object) transfers, but the exact syntax and commit behavior should be checked per engine before relying on it.

Direct answer: Start the recursion at the leaf (the employee whose chain you want), climb to their manager one join at a time, and stop when either the manager chain runs out (reached the top), a depth cap is hit, or a manager id you've already visited on this path shows up again. A recursive common table expression (CTE), written with WITH RECURSIVE, has two parts: an anchor query that seeds the starting rows, and a recursive term that repeatedly joins the CTE back to the base table until nothing new is produced or a stopping condition fires.

Approach

Anchor at every employee; recursive term joins to that employee's manager, prepending the manager's name to build the path and appending the manager's id to a visited-id array to guard against cycles.

WITH RECURSIVE reporting_chain AS (
  -- anchor: every employee starts as their own chain of length 1
  SELECT
    employee_id AS start_id,
    employee_id,
    manager_id,
    name::VARCHAR AS path,
    1 AS depth,
    ARRAY[employee_id] AS visited
  FROM employees

  UNION ALL

  -- recursive term: climb one level to the manager, prepend their name
  SELECT
    rc.start_id,
    m.employee_id,
    m.manager_id,
    m.name || ' > ' || rc.path,
    rc.depth + 1,
    rc.visited || m.employee_id
  FROM reporting_chain rc
  JOIN employees m ON m.employee_id = rc.manager_id
  WHERE rc.depth < 10                       -- depth cap
    AND NOT (m.employee_id = ANY (rc.visited))  -- cycle guard
),
final_chain AS (
  SELECT start_id, path, depth,
    ROW_NUMBER() OVER (PARTITION BY start_id ORDER BY depth DESC) AS rn
  FROM reporting_chain
)
SELECT start_id AS employee_id, path AS reporting_path, depth
FROM final_chain
WHERE rn = 1
ORDER BY employee_id;

Key points

• UNION ALL, not UNION: the recursive term is expected to keep producing new (start_id, employee_id) pairs at increasing depth; UNION would force a distinctness check across every column on every iteration, which is both unnecessary (the visited-array guard already prevents true infinite loops) and expensive.
• The depth cap (rc.depth < 10) belongs in the recursive term's WHERE, not as a post-hoc LIMIT, because LIMIT on the final result doesn't stop the recursion itself from running arbitrarily deep first.
• ROW_NUMBER() ... ORDER BY depth DESC, filtered to rn = 1, picks each employee's longest (i.e., most complete) chain out of the intermediate partial chains the recursion necessarily also produces along the way.
• PostgreSQL requires the RECURSIVE keyword (WITH RECURSIVE); SQL Server's WITH does not use it at all, so this exact syntax is not portable as written across those two engines.

Worked example

Employees: (1, NULL, 'CEO'), (2, 1, 'VP'), (3, 2, 'Manager'), (4, 3, 'Employee').

Verified in PostgreSQL, the query returns:

Employee 4's path matches the target string exactly: CEO > VP > Manager > Employee.

graph TD
  CEO --> VP
  VP --> Manager
  Manager --> Employee

Complexity
Each recursive step is a join from the current frontier of rows back to employees on manager_id; with an index on employees(manager_id) (or the id used to join upward), each step costs proportional to the number of rows at that depth. Overall cost is bounded by (number of employees) x (average chain depth), since in a genuine org chart every employee contributes exactly one row per depth level of their own chain; the depth cap turns a potential unbounded cost into a hard ceiling regardless of how deep or malformed the underlying data is.

Edge cases

• Cycle in the data (e.g., a bad edit makes employee A report to employee B who reports back to A): verified by testing with (2,3,'Alice'), (3,2,'Bob'), (4,2,'Carl') as the sole rows. The recursion for employee 4 produces depth-1 through depth-3 rows and then correctly stops itself once climbing from manager 3 would revisit employee 2, which is already in the visited array; it never reaches the depth-10 cap and never loops.
• A chain longer than the depth cap: silently truncated at 10 levels; if that's a real risk in your org data, surface a flag on rows that hit the cap rather than letting a truncated chain look identical to a genuinely complete one.
• An employee with manager_id IS NULL (the CEO): the anchor row for such an employee is already their complete, correct one-row chain; the recursive term simply never matches for them since there's no manager row to join to.
• If the target engine lacks array types, substitute a delimiter-separated string for visited and check membership with a LIKE pattern (e.g., '|' || rc.visited || '|' LIKE '%|' || m.employee_id || '|%'); it's less type-safe than an array but portable to engines without array support.

Trade-offs & pitfalls

The same shape, sometimes named level or distance from root instead of depth, and sometimes carrying an extra manager_name column pulled straight off each recursive step rather than folded into a path string, shows up repeatedly as the standard org-chart interview pattern; the depth counter, cycle guard, and anchor-plus-recursive-term structure are the substance being tested, the exact column names are cosmetic. For a hierarchy that's queried often but changes rarely (an org chart isn't restructured every minute), consider materializing the flattened chain into a table refreshed on a schedule or on write, rather than recomputing this recursive CTE on every dashboard load.

Direct answer: Cohort lifetime value (LTV, the cumulative revenue a customer generates over time) is a running total per customer since their signup, rolled up to a cohort level at fixed day offsets, and the two wrinkles the question adds both attack the same weak point: a customer with no purchase at all still has to count in the cohort's denominator (via COALESCE(...,0), not by being silently absent), and a purchase in a foreign currency has to be converted using the exchange rate that was actually in effect on the purchase date, not today's rate. Both are as-of lookups: the currency one is the same LATERAL "greatest lower bound" pattern used for point-in-time feature lookups elsewhere, applied to an fx_rates table keyed by (currency, valid_from). That table has to carry a rate for every currency that can appear in purchases, including the home currency itself: a purchase already in USD still needs a matching fx_rates row (rate 1.00) or the join finds nothing and silently converts it to NULL. For scale, the lever is the same one that shows up in every cohort question here: pre-aggregate to (customer, day-offset) once, and don't re-walk each customer's entire purchase history inside a live per-request window function.

Structured elaboration

Currency conversion as an as-of LATERAL join:

SELECT p.purchase_id, p.customer_id, p.purchase_date, p.amount, p.currency,
       fx.rate_to_usd,
       -- COALESCE only covers the home currency: if a genuinely foreign
       -- currency is missing a rate, amount_usd should stay NULL and
       -- surface as a data problem, not silently default to a 1:1 rate
       COALESCE(fx.rate_to_usd, CASE WHEN p.currency = 'USD' THEN 1 END) AS effective_rate,
       p.amount * COALESCE(fx.rate_to_usd, CASE WHEN p.currency = 'USD' THEN 1 END) AS amount_usd
FROM purchases p
LEFT JOIN LATERAL (
  SELECT rate_to_usd
  FROM fx_rates f
  WHERE f.currency = p.currency AND f.valid_from <= p.purchase_date
  ORDER BY f.valid_from DESC
  LIMIT 1
) fx ON true;

This is the exact "latest snapshot at or before this timestamp" shape used for point-in-time feature lookups: for each purchase, find the newest fx rate row that was already in effect on the purchase date, never a rate published later. Getting this backwards (joining to the nearest rate in either direction, or always using today's rate) silently misprices historical revenue whenever a rate has since moved. The precondition this whole join depends on: fx_rates needs a row for every (currency) that shows up in purchases, including the home currency. The cleanest way to guarantee that is to seed fx_rates with an explicit identity row for the home currency (('USD', <earliest date you'd ever query>, 1.00)), so USD purchases are covered by the same as-of join as everything else instead of relying on a query-side special case. The COALESCE(..., CASE WHEN p.currency = 'USD' THEN 1 END) above is a defensive second layer for exactly that one case; it deliberately does not default non-USD currencies to 1.00, because a missing rate for a real foreign currency is a data-quality bug that should produce a visible NULL, not a silently wrong 1:1 conversion.

Cohort LTV at day cutoffs, built on the converted amounts:

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('month', signup_date) AS cohort_month, signup_date
  FROM customers
),
converted AS ( /* the LATERAL currency conversion above, applied to every purchase */ ),
with_offset AS (
  SELECT c.customer_id, c.cohort_month, v.purchase_date, v.amount_usd,
         DATE_DIFF('day', c.signup_date, v.purchase_date) AS days_since_signup
  FROM converted v JOIN cohorts c USING (customer_id)
),
cumulative AS (
  SELECT customer_id, cohort_month, days_since_signup,
         SUM(amount_usd) OVER (PARTITION BY customer_id ORDER BY days_since_signup
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_usd
  FROM with_offset
),
at_cutoffs AS (
  SELECT customer_id, cohort_month,
         MAX(CASE WHEN days_since_signup <= 0  THEN cum_usd END) AS rev_day_0,
         MAX(CASE WHEN days_since_signup <= 30 THEN cum_usd END) AS rev_day_30
  FROM cumulative GROUP BY customer_id, cohort_month
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size FROM cohorts GROUP BY cohort_month
)
SELECT a.cohort_month, cs.cohort_size,
       SUM(COALESCE(a.rev_day_0,0))  AS cohort_rev_day_0,
       SUM(COALESCE(a.rev_day_30,0)) AS cohort_rev_day_30,
       SUM(COALESCE(a.rev_day_30,0)) / cs.cohort_size AS ltv_day_30_per_customer
FROM at_cutoffs a JOIN cohort_sizes cs USING (cohort_month)
GROUP BY a.cohort_month, cs.cohort_size;

The critical detail for sparse cohorts: cohort_sizes is derived from the customers table (everyone who signed up that month), not from at_cutoffs (only customers who bought something). Joining and then COALESCE-ing to 0 means a customer with zero purchases correctly drags the cohort's average down instead of being invisible to the denominator, which is the single most common bug in a from-scratch LTV query: computing the average over "customers who bought something" instead of "customers who signed up."

Median/average time to first purchase per cohort (the related metric the same underlying data supports): once you have each customer's first purchase date, DATE_DIFF('day', signup_date, first_purchase_date) per customer feeds straight into AVG() and MEDIAN() (or PERCENTILE_CONT(0.5)) grouped by cohort. Average and median tell different stories here: a handful of very-late first purchases pulls the average up while barely moving the median, which is worth surfacing explicitly rather than reporting only one of the two.

Scaling beyond a per-user window over each user's whole lifetime: the running SUM() OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) in the query above is correct but re-walks each customer's full purchase history to answer "cumulative revenue at day 30." At real scale, pre-aggregate purchases into a compact (customer_id, days_since_signup_bucket, amount_usd) table once (nightly, incrementally, using the same "only touch what changed" pattern as any other cohort pipeline here), and compute the cutoff sums from that pre-aggregated table instead of scanning raw purchases with a live window function on every run.

Worked example (executed in DuckDB)

CREATE TABLE customers(customer_id INT, signup_date DATE);
INSERT INTO customers VALUES
  (1, DATE '2026-01-01'),  -- January cohort
  (2, DATE '2026-01-10'),  -- January cohort
  (3, DATE '2026-02-01');  -- February cohort

CREATE TABLE purchases(purchase_id INT, customer_id INT, purchase_date DATE, amount DOUBLE, currency VARCHAR);
INSERT INTO purchases VALUES
  (1, 1, DATE '2026-01-01', 100, 'USD'),  -- customer 1, day 0, USD
  (2, 1, DATE '2026-01-20', 50,  'EUR'),  -- customer 1, day 19, EUR
  (3, 2, DATE '2026-01-14', 30,  'USD'),  -- customer 2, day 4, USD
  (4, 3, DATE '2026-02-02', 80,  'USD'); -- customer 3, day 1, USD

CREATE TABLE fx_rates(currency VARCHAR, valid_from DATE, rate_to_usd DOUBLE);
INSERT INTO fx_rates VALUES
  ('USD', DATE '2000-01-01', 1.00),  -- home-currency identity row: belt-and-braces given the COALESCE guard below (verified: removing this row changes none of the output), kept so USD has a visible, queryable rate instead of an invisible query-side default
  ('EUR', DATE '2025-12-01', 1.05),
  ('EUR', DATE '2026-01-15', 1.08);

Two customers signed up in January (customer 1: a USD purchase on day 0 and a EUR purchase 19 days later; customer 2: a USD purchase 4 days after signup); one customer signed up in February with a single USD purchase the next day. All three USD purchases match the ('USD', 2000-01-01, 1.00) identity row via the as-of join, converting 1:1. The EUR purchase on 2026-01-20 as-of-joins to the 2026-01-15 rate (1.08), since that's the newest EUR rate already in effect on the purchase date, converting 50 EUR to 54 USD.

Running the full pipeline against this fixture: the January cohort (cohort_size = 2) gives cohort_rev_day_0 = 100 (only customer 1's day-0 purchase counts at that cutoff) and cohort_rev_day_30 = 184 (customer 1's 100 + 54 = 154, plus customer 2's 30), giving ltv_day_30_per_customer = 92. The February cohort (cohort_size = 1) gives cohort_rev_day_0 = 0 (customer 3's first purchase is one day after signup, so it doesn't count at the day-0 cutoff) and cohort_rev_day_30 = 80, confirming the day-0/day-30 cutoff logic and the currency conversion both behave as specified. Deleting the ('USD', ...) identity row and re-running the exact pipeline above changes nothing: cohort_rev_day_30 stays 184 and ltv_day_30_per_customer stays 92, because the COALESCE(fx.rate_to_usd, CASE WHEN p.currency = 'USD' THEN 1 END) guard supplies effective_rate = 1 for USD purchases whether or not a matching fx_rates row exists; the identity row is belt-and-braces, not required, precisely because that guard exists. What the guard deliberately does not cover is a missing rate for a genuine foreign currency: deleting the two EUR rows instead (leaving the USD row in place) drops the January cohort's cohort_rev_day_30 from 184 to 130 and ltv_day_30_per_customer from 92 to 65, because the EUR purchase's amount_usd comes back NULL and SUM silently drops it. That is the real failure mode this pattern needs a safeguard against, and it is exactly the gap the guard's home-currency-only scoping is designed to still surface as a visible NULL instead of a wrong number.

Trade-offs & pitfalls

• The single most common correctness bug is basing cohort_size on customers who purchased instead of all customers who signed up; it silently inflates LTV for cohorts with many non-buyers.
• Using today's fx rate (or the purchase amount's own currency's latest rate) for a historical purchase misprices past revenue any time a currency has moved since; the as-of join must filter to rates already in effect on the purchase date.
• The fx_rates table must carry a row for every foreign currency that appears in purchases. The home currency is covered even without a matching row, by the COALESCE(..., CASE WHEN p.currency = 'USD' THEN 1 END) guard (verified: deleting the USD identity row changes no output). A missing rate for a genuine foreign currency is NOT covered by that guard, and because the join is a LEFT JOIN LATERAL, the failure is silent (a NULL rate_to_usd that propagates to a NULL amount_usd for that one purchase, not an error), so a real gap in fx_rates can sit undetected until someone notices a cohort's revenue total is lower than expected. Verified directly: removing the EUR rows from the worked example's fixture drops the January cohort's cohort_rev_day_30 from 184 to 130 and ltv_day_30_per_customer from 92 to 65.
• Average time-to-first-purchase is skewed by outliers in a way median is not; reporting only one of the two hides that shape from stakeholders.
• A per-request window function over a customer's entire purchase history is fine at moderate scale but becomes the bottleneck as purchase volume grows; the fix is the same pre-aggregation discipline used throughout cohort analysis, not a cleverer single query.