Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer

Compute a deterministic running total (tie-broken order, since created_at alone can repeat), then locate the row where it first exceeds the threshold using MIN(CASE WHEN running_total > threshold THEN rn END) OVER (PARTITION BY user_id), and mark every row from that point onward as flagged by comparing each row's own tie-broken position to that stored first-crossing marker. The key design choice is comparing against the first-crossing marker, not re-checking running_total > threshold on every row directly: once a row is marked, it stays marked even if a later refund or a NULL amount temporarily pulls the running total back under the threshold, which is exactly what "don't flicker" requires.

Structured elaboration

• Deterministic ordering: ORDER BY created_at, order_id inside every window clause, so ties on created_at resolve the same way every time the query runs; without a tie-breaker, two rows with an identical timestamp have an engine-dependent relative order, and the running total (and therefore the flag) is not reproducible.
• NULL amounts: SUM() (and MIN(), used below) already skip NULL values on their own, so a NULL amount does not poison the running total even without an explicit COALESCE. COALESCE(amount, 0) is still worth writing, not because the math needs it, but because it makes the intended treatment of missing amounts explicit in the query rather than relying on an aggregate's default behavior that a future reader might not know about.
• First-crossing marker: MIN(CASE WHEN running_total > 10000 THEN rn END) OVER (PARTITION BY user_id) relies on the same NULL-skipping behavior; the CASE produces NULL for every row that has not yet crossed the threshold, and MIN ignores those NULLs, landing on the smallest rn where the condition was true.
• Sticky flag: rn >= first_exceed_rn stays TRUE for every row from the first crossing onward, regardless of what the running total does afterward. This is what prevents flicker: the flag is locked onto a position, not re-derived from a live comparison each row.

Worked example

WITH prep AS (
  SELECT order_id, user_id, COALESCE(amount, 0) AS amount, created_at
  FROM orders
),
ordered AS (
  SELECT order_id, user_id, amount, created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at, order_id) AS rn,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at, order_id
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
  FROM prep
),
flagged AS (
  SELECT *, MIN(CASE WHEN running_total > 10000 THEN rn END) OVER (PARTITION BY user_id) AS first_exceed_rn
  FROM ordered
)
SELECT order_id, created_at, amount, running_total,
  CASE WHEN first_exceed_rn IS NOT NULL AND rn >= first_exceed_rn THEN TRUE ELSE FALSE END AS threshold_reached
FROM flagged ORDER BY order_id;

Executed against 5 orders for one user, including a NULL amount and a same-timestamp tie: order 1 (4000, running total 4000), orders 2 and 3 tied on the same created_at (3000 then 2500, tie-broken by order_id, running totals 7000 then 9500), order 4 (NULL amount, running total unchanged at 9500), order 5 (1000, running total 10500). The flag is false through order 4 and flips to true at order 5, the first row where the running total exceeds 10000.

Trade-offs & pitfalls

• Skipping the tie-breaker on created_at does not just risk a wrong-looking output once; it makes the running total (and therefore exactly which row the flag flips on) non-deterministic across re-runs, which is a correctness bug even when no individual run looks obviously broken.
• Relying on SUM and MIN silently skipping NULL is standard SQL behavior, not an engine-specific quirk, but it is worth stating explicitly in code comments so the next reader does not assume NULL amounts are being dropped from the row set entirely (they are not; only from the aggregate math).
• If the actual requirement is a "reactive" flag that un-sets when the running total dips back under the threshold (say, after a large refund), compare running_total > threshold directly per row instead of locking onto the first-crossing marker; that alternative is simpler but is exactly what reintroduces flicker if amounts can go negative.
• An index on (user_id, created_at, order_id) keeps the window functions' per-partition sort and scan cheap on large tables; without it, every partition's ordering work falls back to an in-memory sort.

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.

Approach
I would build a date spine, which is a table of every calendar date in the reporting range, then cross join it to the product list. That guarantees zero-sales days are present. After that, I would left join the daily sales fact and use a running sum partitioned by product and month.

WITH spine AS (
  SELECT d::date AS dt
  FROM generate_series(date '2025-01-01', date '2025-01-31', interval '1 day') AS g(d)
), daily AS (
  SELECT product_id, sale_date, SUM(revenue) AS revenue
  FROM sales_fact
  GROUP BY product_id, sale_date
), base AS (
  SELECT p.product_id, s.dt
  FROM products p
  CROSS JOIN spine s
)
SELECT
  b.product_id,
  b.dt,
  COALESCE(d.revenue, 0) AS revenue,
  SUM(COALESCE(d.revenue, 0)) OVER (
    PARTITION BY b.product_id, DATE_TRUNC('month', b.dt)
    ORDER BY b.dt
  ) AS mtd_revenue
FROM base b
LEFT JOIN daily d
  ON d.product_id = b.product_id
 AND d.sale_date = b.dt;

Reference data needed

• A product dimension.
• A calendar or date spine table, or a generated date series.

Why it works
Partitioning by month resets the running total at each boundary, and the spine ensures missing product-day combinations still show as zero.

Direct answer: For "does a matching row exist in B" checks between two large tables, reach for EXISTS / NOT EXISTS (or the equivalent LEFT JOIN ... IS NULL anti-join) over a plain JOIN or IN/NOT IN. EXISTS returns a boolean per row in A and stops scanning B at the first match, so it never duplicates A's rows even when B has duplicate keys, and it is not vulnerable to the NULL-in-subquery bug that makes NOT IN silently return zero rows. IN with a subquery is usually fine because modern optimizers rewrite it into the same semi-join plan as EXISTS; the real memory risk is a literal IN list built in application code and pasted into the query, not a subquery. Whichever pattern you pick, an index on B's join key is what actually determines whether the query is fast; without one, everything degrades to a scan of B per row of A.

Structured elaboration

Duplicates in B. This is the concrete failure mode that separates EXISTS from a plain JOIN. If B (say, a fraud_events table) has two rows flagging the same user for two different reasons, an INNER JOIN between users and fraud_events returns that user twice, and any downstream COUNT(*) or report built on top silently double-counts them. EXISTS only ever asks "does at least one row match," so the user appears once no matter how many fraud events they have.

The anti-join at scale (removing fraud users). Say you need every user who is NOT flagged for fraud, out of hundreds of millions of users and a smaller but still large fraud_events table where a user can appear more than once. NOT EXISTS and LEFT JOIN ... IS NULL are the two safe shapes; NOT IN is not, because of the NULL trap below. With an index on fraud_events(user_id), both NOT EXISTS and LEFT JOIN ... IS NULL push down to an anti-join plan (hash anti-join or merge anti-join) that touches each fraud_events row once, rather than re-scanning it per user.

The WHERE id IN (subquery) performance failure mode is not what most people think. IN with a correlated or uncorrelated subquery is generally rewritten by the optimizer into the same semi-join plan EXISTS would produce, so on its own it usually is not a memory risk. The failure mode that actually causes memory/parse blowups is different: application code that assembles a literal WHERE id IN (1, 2, 3, ..., 2000000) list and sends it as one giant SQL statement. That list has to be parsed, planned, and often materialized as a hash set client- and server-side, and on some engines a very long literal IN list stops the planner from using an index efficiently at all. The fix is structural, not syntactic: load the id list into a temp table or a values-table join instead of inlining it as literals.

Worked example (executed in DuckDB)

-- A: 3 users. B: fraud flags, with a duplicate on user 2 and one row with a NULL key.
CREATE TABLE A (id INT, name VARCHAR);
INSERT INTO A VALUES (1,'alice'), (2,'bob'), (3,'carol');

CREATE TABLE B (id INT, a_id INT, reason VARCHAR);
INSERT INTO B VALUES (10,2,'chargeback'), (11,2,'velocity'), (12,3,'chargeback');

-- INNER JOIN duplicates bob (two fraud_events rows for a_id=2)
SELECT a.* FROM A a JOIN B b ON b.a_id = a.id ORDER BY a.id;
-- -> (2,'bob'), (2,'bob'), (3,'carol')   -- bob appears twice

-- EXISTS: bob appears once, regardless of how many B rows match
SELECT a.* FROM A a WHERE EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
-- -> (2,'bob'), (3,'carol')

-- NOT EXISTS / LEFT JOIN ... IS NULL: fraud-free users (both agree)
SELECT a.* FROM A a WHERE NOT EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
SELECT a.* FROM A a LEFT JOIN B b ON b.a_id = a.id WHERE b.a_id IS NULL ORDER BY a.id;
-- both -> (1,'alice')

Now the NULL trap, verified by adding one unlinked fraud row with a NULL a_id (for example an orphaned or system-generated flag row):

INSERT INTO B VALUES (13, NULL, 'unlinked');

-- NOT IN silently returns ZERO rows for every user once B's result set contains a NULL
SELECT a.* FROM A a WHERE a.id NOT IN (SELECT a_id FROM B) ORDER BY a.id;
-- -> (empty result, even though alice is genuinely fraud-free)

-- NOT EXISTS is unaffected by the NULL row
SELECT a.* FROM A a WHERE NOT EXISTS (SELECT 1 FROM B b WHERE b.a_id = a.id) ORDER BY a.id;
-- -> (1,'alice')   -- correct, matches the pre-NULL result

This is the exact mechanism of the trap: a.id NOT IN (1, 2, NULL, 3, 2) evaluates as a.id <> 1 AND a.id <> 2 AND a.id <> NULL AND a.id <> 3 AND a.id <> 2. a.id <> NULL evaluates to UNKNOWN for every row, and ANDing UNKNOWN into the chain makes the whole WHERE clause never evaluate to TRUE, so the query returns nothing at all, for any user, not just the one connected to the NULL row.

Trade-offs & pitfalls

• Never write NOT IN (subquery) unless you can prove the subquery's column is NOT NULL (a NOT NULL constraint, or you add WHERE a_id IS NOT NULL inside the subquery yourself). Default to NOT EXISTS.
• IN (subquery) itself is not dangerous; a giant literal IN list assembled by application code is. If you find yourself building a list of a million ids in code, push it into a temp table and join instead.
• EXISTS needs an index on B's join column to be fast; without one it is a nested-loop scan of B per row of A, which is the same cost profile a naive JOIN without an index would have.
• If B has duplicates and you actually need columns from B (not just existence), you cannot avoid the JOIN, but you can avoid duplication by picking one B row per A row first (a ROW_NUMBER() or DISTINCT ON) before joining.

Direct answer: Reach for a window function whenever the downstream consumer needs to see every original row and a group-level or ordered fact attached to it. GROUP BY collapses N rows into one summary row per group, which is correct when you only need the summary, but it permanently discards which specific rows produced that summary, their order relative to each other, and any row that isn't itself the aggregate result. A window function computes the same kind of aggregate or positional fact but writes it back onto every original row instead of replacing them.

Structured elaboration

Situation 1: rolling or trailing time-window features. You need each user-day's trailing 30-day spend as a feature, but you also need to keep the original daily row (date, that day's own spend, any other daily attributes) for model training or a time-series chart. GROUP BY user would collapse all of a user's days into one row and lose the day-by-day granularity entirely; you could GROUP BY user, day, but that just gives you the single day's own spend back, with no way to see the trailing context on that same row.

SELECT user_id, day, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY day
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS spend_30d
FROM transactions;

What GROUP BY would throw away: every day's individual spend value and its position relative to neighboring days; you'd be left with only a single number per user, not a sequence.

Situation 2: row-to-row comparisons (LAG/LEAD). You need the time since a customer's previous transaction, or the previous transaction's amount, as a feature on the current transaction's row.

SELECT *,
  LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS prev_ts,
  ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS time_since_prev
FROM events;

What GROUP BY would throw away: there is no group-level aggregate that captures "the specific previous row's value" at all; MIN/MAX/AVG summarize across a whole group, they cannot single out "the one row immediately before this one." GROUP BY has no concept of adjacency between rows.

Situation 3: ranking or flagging within a group, kept at row grain. You need to flag each purchase as a customer's first purchase, or rank each purchase by amount within that customer's history, while keeping every purchase row intact for a transaction log or funnel report.

SELECT *,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts) = 1 AS is_first_purchase,
  RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank
FROM purchases;

What GROUP BY would throw away: the identity of which specific row is the first, or how each row ranks against its peers; GROUP BY user_id with MIN(ts) tells you when the first purchase happened but not which purchase row that was or anything else about it (amount, product, channel), because those attributes belonged to a row that GROUP BY has already discarded.

Key points

• The unifying idea: window functions annotate rows, GROUP BY replaces rows with a summary.
• Whenever "I need this detail row, plus some fact computed across its group or neighbors, on the same output row," that's the window-function signal.
• Deduplication and top-N-per-group patterns follow the same shape: rank within a partition, then filter, rather than aggregating the group away.

Worked example

Fixture for events (situation 2's row-to-row comparison):

Running the LAG query above against this data (verified in DuckDB):

Each user's opening row has no predecessor, so prev_ts and time_since_prev come back NULL; every later row carries the previous row's timestamp and the gap since it, and all five rows stay in the output, rather than being collapsed down to two rows (one per user) the way a GROUP BY user_id would.

Trade-offs & pitfalls

Prefer GROUP BY plus a join back to detail rows over a window function when the aggregate itself is expensive or when only a small number of pre-aggregated values are actually needed downstream (e.g., a small lookup table of category totals used by many queries): materializing the aggregate once and joining is often clearer and cheaper than recomputing a window function over the full detail set every time the query runs. A common wrong turn is reaching for a window function purely out of habit when a true GROUP BY summary was all the report ever needed; that adds unnecessary per-row computation and a wider result set for no benefit.