Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer: "Churned in month N" (active in N, silent in N+1 and N+2) is a definition that, by construction, needs two months of future data to evaluate, so a churn_rate table for reporting and a labeled training set for a model use the same underlying activity check but serve two different purposes: the reporting table can only publish a rate for months where N+1 and N+2 have already happened, and a training example's label is legitimately allowed to look forward from its anchor date (that's what makes it a label), while its features must not. The "no leakage" constraint in the second half of this question is about the features you'd compute at the anchor date, not about the label definition itself, which inherently requires knowing what happened after the anchor to be a churn label at all.

Structured elaboration

Monthly churn_rate table:

WITH user_months AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', occurred_at) AS month
  FROM events
),
flags AS (
  SELECT um.user_id, um.month,
         EXISTS (SELECT 1 FROM user_months x WHERE x.user_id = um.user_id
                 AND x.month = um.month + INTERVAL 1 MONTH) AS active_next_1,
         EXISTS (SELECT 1 FROM user_months x WHERE x.user_id = um.user_id
                 AND x.month = um.month + INTERVAL 2 MONTH) AS active_next_2
  FROM user_months um
)
SELECT month,
       COUNT(*) AS active_users,
       SUM(CASE WHEN NOT active_next_1 AND NOT active_next_2 THEN 1 ELSE 0 END) AS churned_users
FROM flags
GROUP BY month
ORDER BY month;

The trap in the trailing months: the two most recent months in your data can never be correctly labeled, because you don't yet have N+1 or N+2 for them. Reporting a churn rate for those months anyway (treating "no future data yet" as "churned") systematically overstates recent churn. Flag those months as not-yet-observable rather than publishing a number for them (e.g., month <= max_month_in_data - INTERVAL 2 MONTH as an observability guard on which rows are safe to report).

A category-segmented variant of the same table (churn rate broken out by plan tier, acquisition channel, or any other user category) needs no new logic, only an extra grouping column: join user_months to a user-category lookup and add that category to both the window comparisons' partitioning and the final GROUP BY month, category. The one thing to watch is denominator size: a category with few users per month produces a churn rate that swings wildly month to month on small counts, which is a presentation and interpretation concern (consider showing the underlying active_users count alongside the rate) more than a query-correctness one.

Adapting to supervised-model labels (one row per anchor date per user): this is the same activity table, split into two independent computations that both key off an anchor_month:

WITH user_months AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', occurred_at) AS month FROM events
),
-- FEATURES: only activity at or before the anchor is visible. This is the actual no-leakage guard.
features AS (
  SELECT um.user_id, um.month AS anchor_month,
         (SELECT COUNT(*) FROM events e
          WHERE e.user_id = um.user_id
            AND e.occurred_at <= um.month + INTERVAL 1 MONTH - INTERVAL 1 DAY
         ) AS events_to_date
  FROM user_months um
),
-- LABEL: whether the user goes silent in the two months AFTER the anchor. This looks forward
-- by definition; that is what a label is. It's only usable for anchors old enough that N+1/N+2 exist.
labels AS (
  SELECT um.user_id, um.month AS anchor_month,
         CASE WHEN
           NOT EXISTS (SELECT 1 FROM user_months x WHERE x.user_id = um.user_id AND x.month = um.month + INTERVAL 1 MONTH)
           AND NOT EXISTS (SELECT 1 FROM user_months x WHERE x.user_id = um.user_id AND x.month = um.month + INTERVAL 2 MONTH)
         THEN 1 ELSE 0 END AS churn_label,
         (SELECT MAX(month) FROM user_months) - INTERVAL 2 MONTH >= um.month AS label_observable
  FROM user_months um
)
SELECT f.user_id, f.anchor_month, f.events_to_date, l.churn_label, l.label_observable
FROM features f JOIN labels l USING (user_id, anchor_month)
ORDER BY f.user_id, f.anchor_month;

The distinction that matters for a training pipeline: events_to_date (a feature) is filtered with occurred_at <= anchor, full stop, because at serving/inference time that is genuinely all the model will ever see. churn_label is computed from months strictly after the anchor, and that's fine for a historical training row, but only for anchors where those future months already exist in your data (label_observable); an anchor from last month can't yet produce a trustworthy label, the same trailing-months problem as the reporting table, now expressed per training example instead of per reporting month.

Worked example (executed in DuckDB)

Three users: user 1 active in January and February (not churned as of January, since Feb = N+1 has activity); user 2 active only in January (churned as of January, no Feb or March activity); user 3 active every month January through March. Running the churn_rate query gives January: 3 active, 1 churned (user 2); the label-generation query confirms user 1's January anchor gets churn_label = 0 with label_observable = true, while user 1's February anchor gets churn_label = 1 but label_observable = false, because the dataset ends in March and a February anchor would need April data (N+2) to be trustworthy. That mismatch, a churn_label of 1 sitting next to label_observable = false, is exactly the row a training pipeline must exclude rather than trust at face value.

Trade-offs & pitfalls

• Confusing "the label looks at future months" with "leakage" is a common overcorrection: excluding N+1/N+2 activity from the label computation would make it impossible to construct a churn label at all. The leakage guard belongs on the features, not the label.
• Every anchor date within the trailing two months of your available data produces an unreliable label; silently including those rows in training data biases the model toward whatever early, incomplete signal happens to look like churn.
• The anchor grid itself is a design choice: one anchor per user per active month (used here) versus a fixed monthly cadence for every user regardless of activity changes both what "no signal yet" versus "true churn" means for a brand-new user with only one month of history.
• DATE_TRUNC plus interval arithmetic here is written in Postgres-style syntax; date-truncation and interval functions differ across engines (e.g. DATEADD/DATEDIFF in SQL Server), so the exact functions need adjusting for your target dialect even though the underlying logic transfers directly.

Direct answer: Pick the tool by matching its physical behavior to what the KPI (key performance indicator) actually needs: a common table expression (CTE, a named subquery written with WITH ... AS (...)) for a one-off analysis you'll run once and throw away, a temp or staging table when the calculation is expensive but the reuse window is short (a single session, a single ETL run, meaning extract-transform-load), and a materialized view (a query whose result is physically stored on disk and refreshed on a schedule or trigger, rather than recomputed on every read) once the same expensive logic is read repeatedly by multiple dashboards and can tolerate being slightly stale. Near-real-time dashboards are the one case where none of the "precompute it" options fit cleanly: they usually need a lean, indexable live query instead, or an incrementally-updated summary table rather than a full materialization.

Structured elaboration

Recommendations by scenario:

• One-off ad hoc analysis: a CTE (or a plain subquery). There is no second reader to amortize setup cost against, so the fastest path to an answer wins over any persistence machinery.
• Repeatedly-used expensive calculation: a materialized view (or, where the engine lacks native materialized views, a scheduled job that populates a plain table). The cost of computing it is paid once per refresh instead of once per dashboard load, and it becomes indexable, which a CTE never is.
• Near-real-time dashboard: avoid full materialization; either query the base tables directly with supporting indexes so the planner can push filters down, or maintain an incrementally-updated summary table (updated on write, not recomputed wholesale) if the underlying computation is too heavy to run live on every page load.

When does a CTE pipeline graduate to a materialized view? Three independent signals, any one of which is usually enough on its own: (1) reuse -- the same CTE logic is being copy-pasted into a second, third, or fourth query or dashboard, which is a sign the definition should live in one place instead of many; (2) performance -- the CTE's underlying computation starts showing up as the dominant cost in an EXPLAIN plan across multiple callers, so the aggregate cost of recomputing it everywhere now exceeds the cost of maintaining a refreshed copy; (3) governance -- different query authors start writing slightly different filters around a copy-pasted CTE, so the "same" KPI silently drifts between dashboards. Any of these is the point to promote the logic into a materialized view (or persisted table) with one refresh schedule and one definition that every consumer reads.

Worked example

A KPI like "monthly active accounts" (distinct accounts with at least one qualifying event in the trailing 30 days) is a good stand-in: it is expensive on a large events table because it needs a distinct count over a rolling window, and it is exactly the kind of metric multiple dashboards want to show.

-- (a) one-off exploration: CTE, thrown away after this single query
WITH active_accounts AS (
  SELECT DISTINCT account_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(*) AS mau FROM active_accounts;

-- (b) repeatedly-used, refresh-tolerant: materialized view, indexed, refreshed nightly
CREATE MATERIALIZED VIEW mv_monthly_active_accounts AS
SELECT account_id, MAX(event_time) AS last_active_at
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY account_id;

CREATE INDEX idx_mv_maa_account ON mv_monthly_active_accounts(account_id);
-- refreshed on a schedule, e.g. REFRESH MATERIALIZED VIEW mv_monthly_active_accounts;

-- (c) near-real-time: query live, relying on an index on events(account_id, event_time)
-- rather than pre-aggregating, so results are current to the second
SELECT COUNT(DISTINCT account_id) AS mau_live
FROM events
WHERE event_time >= NOW() - INTERVAL '30 days';

ML (machine learning) feature pipeline framing: this exact decision tree also applies when the "dashboard" is actually a model training job reading a feature. A feature explored once in a notebook is a CTE; a feature reused across many training runs (and needing point-in-time correctness, i.e. only using data available as of each label's timestamp) is exactly the "repeatedly-used expensive calculation" case, and belongs in a materialized feature table (or a feature store) that is refreshed on a known cadence, not recomputed inline in every training query. The freshness criterion becomes sharper here: a stale feature table used for training is often fine, but the same staleness at serving/inference time can silently create training-serving skew, so the acceptable staleness window has to be decided per use case, not assumed.

Spark SQL / Catalyst optimizer angle: the reuse math is different in Spark. Spark's Catalyst optimizer treats a WITH clause referenced multiple times by inlining and re-planning the underlying logical plan at each reference site by default, rather than computing it once and sharing the result the way a materialized view would. A CTE joined against three times in one Spark SQL query can trigger the same expensive computation three separate times unless you explicitly force sharing (persisting the intermediate DataFrame with .cache()/.persist(), or writing it out and reading it back). This makes the "graduate to materialization" decision arrive sooner in Spark than in a single-node warehouse: a CTE reused even twice within one query is worth checking for repeated work, not just a CTE reused across many separate queries.

Trade-offs & pitfalls

The common wrong turn is reaching for a materialized view purely because a query is slow, without checking whether the consumer can actually tolerate the staleness that comes with it: a materialized view refreshed hourly is the wrong answer for a dashboard that promises "as of right now." Conversely, leaving an expensive, frequently-reused CTE unmaterialized "for simplicity" quietly multiplies its cost by however many dashboards call it, since nothing about a CTE shares work across separate queries. Temp/staging tables sit in between and are easy to over- or under-use: they are the right tool inside a single multi-step job (build once, query several times, then discard), but reaching for one to serve a dashboard means re-inventing a materialized view's refresh logic by hand, usually worse. Always confirm the actual freshness requirement with the stakeholder before choosing: "near-real-time" and "updated every 15 minutes" are very different engineering problems that get conflated in casual requirements language.

A correlated subquery that counts rows from a related table silently overcounts when that table has duplicate rows for the correlation key, because COUNT(column) counts every row that matches, not every distinct value. If order_items has two rows for the same product on the same order (a duplicate import, a split line-item, a merged data source), a query counting "distinct products per order" using plain COUNT will count that duplicated product twice, inflating the total and mis-classifying orders that don't actually meet the threshold.

The bug

-- Wrong when order_items has duplicate rows for the same product
SELECT o.id, o.customer_id
FROM orders o
WHERE (
  SELECT COUNT(oi.product_id)     -- counts every row, including duplicates
  FROM order_items oi
  WHERE oi.order_id = o.id
) > 3;

If an order has products A, B, C but A appears as two separate rows in order_items, this subquery returns 4, and the order is wrongly flagged as having more than 3 distinct products when it actually has 3.

Fix 1: DISTINCT inside the subquery

SELECT o.id, o.customer_id
FROM orders o
WHERE (
  SELECT COUNT(DISTINCT oi.product_id)   -- counts unique products only
  FROM order_items oi
  WHERE oi.order_id = o.id
) > 3;

Fix 2: pre-aggregate with GROUP BY, then JOIN

SELECT o.id, o.customer_id
FROM orders o
JOIN (
  SELECT order_id, COUNT(DISTINCT product_id) AS unique_products
  FROM order_items
  GROUP BY order_id
) t ON t.order_id = o.id
WHERE t.unique_products > 3;

Key points

• COUNT(col) and COUNT(*) both count rows, not distinct values; COUNT(DISTINCT col) is the only one of the three that de-duplicates.
• Fix 1 is the minimal, correct patch and stays correlated, so it still conceptually re-runs the count per outer row.
• Fix 2 pre-aggregates order_items once with GROUP BY, before joining, so it's no longer correlated at all; the join replaces the per-row re-execution with a single aggregation pass plus a join.

Complexity

The correlated fix (Fix 1) still costs one grouped count per outer order, logically O(orders) × O(cost of counting that order's items); an index on order_items(order_id, product_id) keeps each individual count cheap, but the query still repeats work per order unless the optimizer rewrites it. Fix 2 aggregates order_items once, in a single GROUP BY pass over the whole table, then does one join back to orders, which is the same total amount of aggregation work done once instead of potentially once per order, and lets the engine choose a hash or merge join for the join step.

Worked example

Two orders: order 1 has three order_items rows for products A, B, C, but A is duplicated (two rows for the same product); order 2 has four genuinely distinct products D, E, F, G. The buggy query (COUNT without DISTINCT) returns both orders as having more than 3 products, because order 1's row count is 4 even though it only has 3 distinct products. Both fixes correctly return only order 2. (Verified by executing all three queries against SQLite 3.51 with this exact duplicate-row setup.)

Trade-offs and pitfalls

This bug class isn't limited to COUNT: any correlated aggregate that assumes one row per logical entity (a SUM of amounts, an AVG of prices) will silently misbehave the same way if the correlated table has duplicate rows for reasons unrelated to the business logic being modeled. The deeper fix is a data-quality one: if order_items shouldn't have duplicate (order_id, product_id) rows in the first place, add a unique constraint or de-duplicate upstream in the pipeline, rather than teaching every downstream query to defensively DISTINCT around a data problem. Between the two SQL-level fixes, prefer Fix 2 (pre-aggregate then join) once the query runs against a large table or gets reused in multiple places: it does the aggregation exactly once instead of repeating it per outer row, and it separates "what counts as duplicate" logic into one place instead of scattering DISTINCT across every query that touches order_items.

Approach
First aggregate at the raw line-item grain, then rank the product totals within each region. A line item is one row of product revenue, so summing those rows avoids double counting order-level facts. For the tie rule, use DENSE_RANK() or RANK() on the final product totals, then keep ranks <= 3.

WITH product_revenue AS (
  SELECT
    region,
    product_id,
    SUM(line_revenue) AS revenue
  FROM raw_line_items
  GROUP BY region, product_id
), ranked AS (
  SELECT
    region,
    product_id,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY region
      ORDER BY revenue DESC
    ) AS revenue_rank
  FROM product_revenue
)
SELECT region, product_id, revenue
FROM ranked
WHERE revenue_rank <= 3;

Why this is correct

• The aggregation happens before ranking, so each product appears once per region.
• DENSE_RANK() includes every product tied at the cutoff.
• If you also need order-level facts, aggregate them in a separate CTE at order grain and join the finished summaries, not the raw tables.

Worked example
If a region has revenues 100, 90, 90, 80, the ranks are 1, 2, 2, 3. Filtering <= 3 returns all four products, which matches the tie requirement.

The same "latest order per user" result can be built with a correlated subquery, a derived-table join against a pre-aggregated MAX(created_at), or a ROW_NUMBER() window function; they differ in how much per-row work the engine has to redo and in how they handle ties. On a large orders table, the ROW_NUMBER() form is usually the best default because it computes ranks in one ordered pass and lets you break ties deterministically, but the correlated form is still the right call when the predicate is narrow and well-indexed.

Approach 1: correlated subquery

SELECT o.*
FROM orders o
WHERE o.created_at = (
  SELECT MAX(o2.created_at)
  FROM orders o2
  WHERE o2.user_id = o.user_id
);

Approach 2: derived-table join on MAX(created_at)

SELECT o.*
FROM orders o
JOIN (
  SELECT user_id, MAX(created_at) AS max_created
  FROM orders
  GROUP BY user_id
) m ON o.user_id = m.user_id AND o.created_at = m.max_created;

Approach 3: ROW_NUMBER() window function

SELECT order_id, user_id, amount, created_at
FROM (
  SELECT o.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_id DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Key points

• Approaches 1 and 2 both compare against MAX(created_at), so if two orders for the same user share the exact same timestamp, both are returned; neither query has a tiebreaker.
• Approach 3 adds order_id DESC as an explicit tiebreaker, so it always returns exactly one row per user even when created_at collides.
• ROW_NUMBER() can't be filtered directly in a WHERE clause (window functions evaluate after WHERE), which is why it has to be wrapped in a subquery, as shown, or filtered with QUALIFY in engines that support it (Snowflake, BigQuery, Databricks; not PostgreSQL or MySQL).

Complexity

The correlated subquery is, logically, one MAX lookup per outer row; whether that's cheap depends entirely on the plan. With an index on (user_id, created_at DESC), a smart optimizer can turn this into an index-descent per user rather than a full re-scan, but many optimizers instead execute it as a nested loop that repeats the inner scan once per outer row, which is the pattern to watch for in EXPLAIN. The derived-table join computes the per-user MAX once, in a single grouped pass over the table (an index or hash aggregate handles this in roughly linear time), then joins that back, which is a bounded amount of work regardless of how many orders a user has. The ROW_NUMBER() form does one sort (or an index-satisfied ordering) per partition, which is generally the same order of work as the grouped aggregate but produces the tiebreaker and the ordering guarantee in the same pass.

Worked example

With five orders across three users, including two orders for user 101 that share the exact same created_at (a same-second data-entry collision), running all three queries against this data shows the difference directly: approaches 1 and 2 both return two rows for user 101 (the tie), while approach 3 with ORDER BY created_at DESC, order_id DESC returns exactly one deterministic row per user, including user 101. (Verified by executing all three queries against SQLite 3.51 with this exact tie case.)

Trade-offs and pitfalls

Genuinely reach for the correlated form when the predicate is highly selective and backed by the right composite index (e.g., you've already filtered down to a handful of users), because a per-row indexed lookup can be cheaper than materializing a full per-user aggregate you don't need elsewhere; some optimizers (PostgreSQL, SQL Server) can automatically rewrite a correlated subquery like this into a semi-join or hash join, but that isn't guaranteed across engines or even across query shapes on the same engine, so don't assume it happened silently. Always check: if EXPLAIN shows a nested loop re-scanning orders once per outer row instead of a hash or merge join, that's the concrete signal to rewrite into the derived-table or ROW_NUMBER() form. The most common bug across all three forms isn't performance, it's silently dropped or duplicated rows from an unhandled tie; if the business logic genuinely needs "the" single latest order, the ROW_NUMBER() form with an explicit tiebreaker is the only one of the three that guarantees it.