Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer: At hundreds of millions of rows, a windowed DELETE run in small batches and a build-a-deduplicated-copy-then-swap approach are the two realistic options, and they trade off differently: batched deletes touch the live table in place with many short transactions, keeping the table available throughout but taking longer overall and needing careful batching to avoid long-held locks; copy-and-swap builds a clean table from scratch and swaps it in with one fast rename, which is much simpler to reason about and to back out of, at the cost of needing roughly double the disk space and a brief write-blocking window at swap time.

Structured elaboration

Batched DELETE, in detail.

-- Step 0: snapshot before touching anything
CREATE TABLE duplicates_backup AS SELECT * FROM duplicates;

-- Repeat until 0 rows affected: delete a bounded batch of losers per run
WITH to_delete AS (
  SELECT id
  FROM (
    SELECT id,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at ASC, id ASC) AS rn
    FROM duplicates
  ) d
  WHERE d.rn > 1
  LIMIT 1000
)
DELETE FROM duplicates WHERE id IN (SELECT id FROM to_delete);

Run this in a loop from outside the database (a script that keeps calling it until the affected-row count is 0), each iteration as its own short transaction. This keeps any single lock hold time bounded and predictable, which matters on a table other processes are actively reading from or writing to.

Copy-and-swap, in detail.

CREATE TABLE duplicates_new AS
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at ASC, id ASC) AS rn
  FROM duplicates
) d
WHERE rn = 1;

-- rebuild indexes/constraints on duplicates_new to match the original, then:
ALTER TABLE duplicates RENAME TO duplicates_old;
ALTER TABLE duplicates_new RENAME TO duplicates;
-- keep duplicates_old around until confident, then drop it

Keeping the N most recent rows instead of exactly 1. Both approaches generalize directly: change the filter from rn = 1 (or rn > 1 for the delete side) to rn <= 2 / rn > 2 to keep the two most recent rows per key instead of just the latest one. Nothing else about the mechanism changes; it's the same ROW_NUMBER partition, just a different cutoff.

SQL-in-warehouse versus an external job (e.g. Spark) at this scale. For a table already living in a columnar warehouse (Snowflake, BigQuery, Redshift), the copy-and-swap pattern above, run as warehouse SQL, is usually simpler and cheaper: the warehouse's own engine already parallelizes the scan and sort, and you avoid the cost of exporting hundreds of millions of rows to an external cluster and reimporting the result. A separate Spark job earns its complexity when the source table lives in a system without a native, cheap CREATE-TABLE-AS-SELECT-and-swap primitive, or when the dedup logic needs to join against data that only exists outside the warehouse (e.g. an external reference dataset too large to load in cheaply).

Backup, transaction, and rollback considerations for each

• Backup. Snapshot before any destructive operation regardless of approach: a full table copy for copy-and-swap doubles as its own backup (the old table, kept around); for batched delete, take an explicit backup table or rely on point-in-time recovery / WAL retention, since there's no natural 'old table' left behind.
• Transactions. Batched delete needs many small transactions by design, to bound lock duration; copy-and-swap needs exactly one short transaction at the rename step, with the expensive build work happening outside any long-held lock.
• Rollback. Copy-and-swap rollback is a second rename back to the original table, essentially instantaneous. Batched-delete rollback means restoring from the backup table (or WAL-based point-in-time recovery), which is slower and coarser-grained, since individual batches aren't usually tracked well enough to undo just one.

Trade-offs & pitfalls

• Copy-and-swap needs roughly double the storage for the duration of the operation (old + new table coexist); on a genuinely huge table this can be the deciding constraint even when it's operationally simpler.
• A unique constraint (enforced via ON CONFLICT DO NOTHING on PostgreSQL/SQLite, or the dialect's equivalent conflict handling) prevents new duplicates from being written after cleanup, but only after you've already removed the existing ones through one of the two methods above; adding the constraint first, before cleanup, just fails outright on the existing duplicate rows.
• Disable or carefully sequence foreign-key-dependent triggers/cascades before batched deletes on a table other tables reference; deleting a 'loser' row that a child table still points to either fails the batch or silently cascades data loss, depending on the constraint's ON DELETE behavior.

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.

Clarify first
I would ask whether the product manager wants a time-based window or a calendar window. A 168-hour window means the last 7 times 24 hours from each event timestamp. A calendar window means the current day plus the previous 6 calendar days in the store's business timezone. Those are not the same at midnight boundaries.

Implementation choices

• If they want 168 hours, use a timestamp window.
• If they want calendar days, aggregate by date first, then roll up daily counts.

-- Calendar-day version
WITH daily AS (
  SELECT
    store_id,
    CAST(order_ts AS date) AS order_date,
    COUNT(*) AS orders
  FROM orders
  GROUP BY store_id, CAST(order_ts AS date)
)
SELECT
  store_id,
  order_date,
  SUM(orders) OVER (
    PARTITION BY store_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_orders
FROM daily;

Boundary example
An order at 2025-01-08 00:05 UTC is inside the calendar-day window for Jan 8 through Jan 2, but in a strict 168-hour window it depends on the exact timestamp cutoff. I would document that choice in the metric definition so analysts get the same answer.

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.

Direct answer: Aggregate revenue per product, rank within each category with RANK() (not ROW_NUMBER(), so ties share a rank), then filter to rnk <= 3 in an outer query, never inside the same SELECT the window function runs in, since window functions cannot appear in a WHERE clause at all. For the order-of-operations bug: a window function evaluates after FROM/JOIN, WHERE, GROUP BY, and HAVING have already run, so any filter placed in that earlier WHERE clause has already shrunk (or changed) the rowset the window function sees, before the window function's own partition and ranking logic gets to run over what's left. If the intent was "rank across everyone, but only display a subset," that filter has to move to an outer query wrapped around the ranking, applied after the rank is computed, not before.

Approach: top-3-per-category with ties included

WITH product_revenue AS (
  SELECT p.product_id, p.category_id, SUM(s.revenue) AS total_revenue
  FROM products p
  JOIN sales s ON s.product_id = p.product_id
  GROUP BY p.product_id, p.category_id
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) AS rnk
  FROM product_revenue
)
SELECT * FROM ranked WHERE rnk <= 3
ORDER BY category_id, rnk;

Key points

• RANK() is required, not ROW_NUMBER(): it's what lets a tie at the 3rd-place cutoff produce 4 (or more) output rows for that category instead of arbitrarily dropping one of the tied products.
• The rnk <= 3 filter has to be a WHERE on the outer query reading from the ranked common table expression (CTE, a named subquery written with WITH ... AS (...)), not folded into the same SELECT as the RANK() call; attempting WHERE RANK() OVER (...) <= 3 in the same query fails outright, since window functions are evaluated after WHERE and cannot be referenced by it.
• Verified in DuckDB against a category with revenues 100, 90, 80, 80, 10 (a genuine tie at the 3rd-place cutoff): ranks come out 1, 2, 3, 3, 5, and rnk <= 3 correctly returns 4 rows, not 3, since both rank-3 products are included.

Confirming the hard WHERE-with-window-function error directly: running ... WHERE RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) <= 3 in the same SELECT raises Binder Error: WHERE clause cannot contain window functions! in DuckDB (Postgres and most standard-conforming engines reject the same construct for the same reason: the standard's logical query-processing order runs WHERE before the SELECT list, where window functions live, so at the point WHERE is evaluated the window function's result doesn't exist yet).

Approach: the subtler order-of-operations bug

This second failure doesn't error; it silently changes which rows the window function's partition sees. Say the intent is "rank sales reps by total company-wide revenue, but only display West-region reps' ranks":

-- WRONG: filters to West BEFORE the window function runs, so RANK() only
-- ever sees West rows and ranks reps against each other, not against the company
SELECT rep_id, region, total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
FROM rep_revenue
WHERE region = 'West';

-- CORRECT: rank over the full, unfiltered set first, then filter in an outer query
WITH ranked AS (
  SELECT rep_id, region, total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
  FROM rep_revenue
)
SELECT * FROM ranked WHERE region = 'West';

Verified in DuckDB against rep_revenue = (1,West,900), (2,West,500), (3,East,800), (4,East,700), (5,West,300): the wrong version ranks rep 2 as company_rank = 2, since WHERE region = 'West' already removed the two East reps before RANK() ever ran, so it only ever compared West reps against each other. The correct version, which computes RANK() over all 5 reps first and filters afterward, gives rep 2 company_rank = 4 (correctly behind both East reps at 800 and 700), and rep 5 goes from a wrong 3 to a correct 5. Same query shape, same filter, different result, purely because of when the filter runs relative to the window function.

Key points

• This bug never errors: the query executes and returns plausible-looking numbers, which is what makes it dangerous; nothing about the output signals that the ranks were computed against a filtered subset instead of the intended full set.
• The fix is structural, not syntactic: move the filter to an outer query or a later WHERE clause that reads from a CTE or subquery where the window function has already run, so the filter only removes rows after ranking, not before it.
• A related, adjacent issue: if ORDER BY total_revenue DESC alone doesn't fully determine row order (ties, or an equivalent secondary sort key not included), the same query can return a different, equally "valid" ranking on a re-run or across replicas; add a deterministic tiebreaker column (ORDER BY total_revenue DESC, rep_id) so the ranking output is reproducible, independent of whichever physical row order the engine happened to scan in.

Edge cases

• A category (or region) with only one product/rep: ranking and filtering both degenerate correctly to that single row; no special-casing needed.
• Filtering that's actually intended to happen before the window function (e.g., WHERE order_status = 'completed' to exclude cancelled orders from the revenue base entirely) is completely correct in the same position; the bug only exists when the filter is meant to restrict the display of an already-computed ranking, not the population the ranking is computed over. Getting this distinction right is a requirements question, not a syntax one.

Trade-offs & pitfalls

The common wrong turn is treating "the query runs without error" as proof it's correct; the order-of-operations bug specifically produces plausible, wrong numbers with no error signal at all. When reviewing a ranking query, explicitly ask: is every WHERE/JOIN condition meant to change the population being ranked, or meant to filter the display of an already-computed rank? Any condition in the second category belongs in an outer query, never alongside the window function itself.