Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer: "Last 7 readings" is a count-based request, but RANGE BETWEEN is a value-based frame: RANGE BETWEEN INTERVAL '6 seconds' PRECEDING AND CURRENT ROW doesn't mean "6 rows back," it means "every row whose timestamp is within 6 seconds of this row's timestamp." On irregular data that value window can contain anywhere from zero rows (during a quiet stretch) to dozens (during a burst), so no matter what interval you plug in, RANGE cannot deliver a stable "last 7 readings" semantic. The robust alternative is a purely positional frame, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which always includes exactly the 7 most recent physical rows in sort order, independent of how close together or far apart their timestamps happen to be.

Structured elaboration

• Both frame types accept syntax that looks parallel (BETWEEN 6 PRECEDING AND CURRENT ROW), so it's easy to assume the "6" means the same thing in each. It doesn't: ROWS's number is a row count; RANGE's number (or interval) is a distance in the units of whatever you ORDER BY.
• On genuinely irregular timestamps, a RANGE frame's row count swings with local reading density: a burst of readings a second apart puts many rows inside even a small interval; a gap of hours puts zero preceding rows inside that same interval. There is no fixed interval value that reliably captures "the last 7" across both regimes, because RANGE was never counting rows in the first place.
• The dangerous part is that this fails silently. There's no error, no exception, just a differently-sized sum or average computed at every row depending on local reading density. It shows up downstream as a suspiciously spiky or suspiciously flat metric, not a crash, which makes it slow to diagnose.

Worked example

Table readings(id, ts, value): a burst of 4 readings 1 second apart, then a 3-hour gap, then 4 more readings 1 second apart.

SELECT id, ts, value,
  COUNT(*) OVER (ORDER BY ts
    RANGE BETWEEN INTERVAL '6 seconds' PRECEDING AND CURRENT ROW) AS n_range_6s,
  COUNT(*) OVER (ORDER BY ts
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS n_rows_7
FROM readings
ORDER BY ts;

Verified in DuckDB. n_range_6s comes back 1, 2, 3, 4, 1, 2, 3, 4: it resets to 1 right after the 3-hour gap even though that row is the 5th reading overall, because the 3-hour gap (10,800 seconds) is far outside the 6-second value window, so RANGE genuinely finds nothing preceding it within range. n_rows_7 comes back 1, 2, 3, 4, 5, 6, 7, 7: a smooth, always-correct count of the most recent readings, capped at 7, with no sensitivity to how close together the timestamps are.

Key points

• ROWS's bound is a count; RANGE's bound is a distance, and confusing the two is the entire trap.
• No choice of interval fixes RANGE for a count-based requirement on genuinely irregular data: shrink it and you miss rows during quiet stretches, widen it and you pull in far too many during bursts.
• The query never errors either way, so this bug is discovered from a metric that looks wrong, not from a stack trace.

Complexity
ROWS with a fixed count of preceding rows is O(n) after the initial O(n log n) sort: an engine can maintain the sum incrementally, dropping the row that falls off the trailing edge as it adds the new one. RANGE frames over irregular data are also computed incrementally by most engines, but because the number of rows in frame varies row to row, the per-row work is proportional to how many rows enter or leave the frame at that step, which is data-dependent rather than a fixed constant.

Edge cases

• Fewer than 7 total readings so far: both frames just use however many rows exist; this isn't a bug in either version, it's the expected boundary behavior at the start of the series.
• Duplicate timestamps: RANGE treats exactly-equal timestamps as peers (all included or all excluded together), which can push its row count up further inside a dense burst; ROWS is unaffected since it only cares about physical position.
• If the ordering column is a raw numeric epoch rather than a proper TIMESTAMP, the RANGE trap is identical, just with the units being "whatever the number represents" instead of an explicit interval; the type of the column doesn't change the underlying value-vs-count distinction.

Trade-offs & pitfalls

This isn't "always prefer ROWS." If the actual requirement is a genuine time-bounded window ("everything from the last 10 minutes," not "the last 7 readings"), RANGE with an appropriately chosen interval is the correct tool and ROWS is wrong for that job. Match the frame type to what the requirement is actually counting: a quantity of readings, or a span of time.

A common middle-ground need is "the last 7 readings, but only if they're within some sane recency window," so a stale burst from last week doesn't get blended with today's value. Neither ROWS nor RANGE alone expresses "both a max count and a max time span"; that needs ROWS for the count plus a separate filter (WHERE or HAVING) checking the oldest included timestamp, or a two-pass approach.

This is a different failure shape from a series with a handful of whole missing calendar days, where the frame boundary and the business unit of "day" still line up cleanly and only the sample size shrinks. Here the irregularity is sub-day and unbounded in either direction, which is what makes RANGE unusable for a count-based ask no matter how the interval is tuned.

Direct answer: Yes, computing a window function almost always requires sorting the input by its PARTITION BY and ORDER BY columns first, because the engine needs rows grouped by partition and ordered within each partition before it can walk them and apply a frame. "Partition-wise" means the engine scopes both that sort and the running window state to one partition at a time, so partitions don't interfere with each other. An expensive window operation shows up in an EXPLAIN plan as a dedicated sort step feeding into a window-aggregate step, and when the sort or the per-partition state can't fit in memory, the plan (or its runtime statistics) will show spilling to disk. In a distributed engine, the PARTITION BY/ORDER BY columns also determine how rows get redistributed (shuffled) across worker nodes before the local sort and window computation can even start, and that shuffle is often the most expensive part of the whole operation.

Structured elaboration

• Why a sort is needed: a window frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW or the default RANGE frame is defined relative to a row's position or value within its partition, which only means something once rows are laid out in a known order. If the source data isn't already physically sorted or index-ordered that way, the engine performs an explicit sort before evaluating the window. If an index (or the table's physical clustering) already produces rows in PARTITION BY, ORDER BY order, some engines can skip the sort and read directly in that order.
• Partition-wise processing: once sorted, the engine processes one partition's rows as a self-contained unit, maintaining just enough state (a running accumulator for a cumulative sum, a buffer of in-frame rows for a moving average) to compute each row's window value, then resets that state when the partition key changes. This is why a window function's cost scales with partition size and count, not just total row count: many small partitions are cheap per partition but add per-partition setup overhead, while a few huge partitions concentrate all the memory pressure into a small number of large working sets.
• Memory usage and spill: frames that only ever need a small trailing slice of rows (like a bounded ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) can be computed with a small, fixed-size sliding buffer. Frames that need the whole partition in memory at once (like RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or any function that needs to see the partition's max/last value from every row) require buffering proportional to partition size. When that buffer or the preceding sort exceeds the memory budget the engine allotted to the operation, both spill to temporary disk storage, which is dramatically slower than in-memory processing because it adds disk I/O on top of the comparison work.
• What shows up in EXPLAIN: in engines with a Postgres-style plan (Postgres itself, and several Postgres-compatible warehouses), an expensive window query's plan shows an explicit Sort node feeding a WindowAgg node; running with EXPLAIN (ANALYZE, BUFFERS) additionally reports temporary file usage if the sort or the window state spilled. A high gap between the planner's estimated row count and the actual row count at the Sort/WindowAgg node is a strong early signal that memory was sized for the wrong volume.

Worked example

Running EXPLAIN on a window query against a 5,000-row synthetic table in DuckDB (partitioned into 50 users, so 100 rows per partition on average) confirms the shape described above:

EXPLAIN SELECT user_id, ts, amount,
  SUM(amount) OVER (PARTITION BY user_id ORDER BY ts) AS running
FROM tx;

Verified in DuckDB, the physical plan is:

PROJECTION (~5,000 rows)
  -> WINDOW  (sum(amount) OVER (PARTITION BY user_id ORDER BY ts ASC NULLS LAST))
    -> SEQ_SCAN  (Table: tx, ~5,000 rows)

DuckDB folds the sort into its WINDOW operator's internal implementation rather than showing a separate Sort node the way Postgres does, but the underlying work is the same: a sequential scan feeds an operator that must group and order rows by user_id, ts before it can compute a running sum. In Postgres, the equivalent plan for the same query would show WindowAgg sitting directly on top of an explicit Sort node (itself on top of a Seq Scan or Index Scan), and EXPLAIN (ANALYZE, BUFFERS) would report any temp files the Sort had to write if the partitioned data didn't fit in work_mem.

Key points

• The default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) tends to need more buffered state than a bounded frame like ROWS BETWEEN N PRECEDING AND CURRENT ROW, because value-based framing has to track peer groups (rows that share the same ORDER BY value, and so must move in and out of the frame together) rather than a fixed-size sliding window.
• A sort that's already satisfied by an index on (partition_columns, order_column) can let the engine skip a separate sort step entirely; this is the single biggest lever for making a window query cheap.
• Spilling isn't unique to window functions: it's the same generic mechanism engines use for any operation (sort, hash join, hash aggregate) that needs more working memory than was allotted; the fix (raise the memory budget for that operation, or shrink the working set) is the same in every case.

Trade-offs & pitfalls

Distributed engines (BigQuery, Snowflake, Redshift): these engines store and process data across many worker nodes, so before a partition-wise sort can even begin, rows belonging to the same PARTITION BY key generally need to be co-located on the same node. That redistribution step (commonly called a shuffle, exchange, or repartition stage depending on the engine) moves data across the network and is frequently the dominant cost of an expensive distributed window query, more so than the local sort or window computation that follows it. Look for a shuffle/exchange/repartition stage appearing immediately before the sort-and-window stage in the distributed query plan; its cost scales with the volume of data moved across nodes, not with the number of rows in any single partition. A skewed partition key (a few PARTITION BY values covering a hugely disproportionate share of rows, like one user_id with millions of events against a median of dozens) causes uneven work distribution after the shuffle: most workers finish quickly while one worker is stuck processing a huge partition alone, and total query time is bounded by that slowest worker, not by the average partition size.

The practical mitigation playbook, roughly in order of effort: check for partition-key skew first (a query grouping by the partition key and counting rows quickly reveals it), because no amount of indexing fixes a genuinely lopsided key; then check whether an index or clustering key already matches PARTITION BY, ORDER BY so the sort can be skipped or cheapened; then, if the frame only needs a bounded trailing window, confirm the query actually specifies ROWS BETWEEN N PRECEDING rather than relying on the unbounded default, since that alone can shrink the per-row memory footprint substantially.

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.

Direct answer: Sort each user's events by timestamp, use LAG to get the previous event's time, and flag a new session whenever the gap exceeds 30 minutes (or there is no previous event at all). A running SUM of that flag, ordered the same way, assigns a stable session number to every row: it stays flat within a session and steps up by one at each boundary. Group by (user, session number) to collapse each session into its start, end, and event count.

Structured elaboration

1. Flag session starts. For each user, compare the current event's timestamp to the previous one via LAG. If the gap is more than 30 minutes, or there is no previous row (the user's first event), mark it as a new session start.
2. Number the sessions. A cumulative SUM of that 0/1 flag, ordered by time within the user partition, never resets and only increases at a boundary, so every row between two boundaries carries the same running total. That running total IS the session id.
3. Aggregate. GROUP BY user_id, session_no collapses each session down to MIN(event_ts), MAX(event_ts), and COUNT(*).

WITH flagged AS (
  SELECT user_id, event_ts,
    CASE WHEN LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
           OR event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) > INTERVAL '30 minutes'
         THEN 1 ELSE 0 END AS is_session_start
  FROM events
),
numbered AS (
  SELECT user_id, event_ts,
    SUM(is_session_start) OVER (PARTITION BY user_id ORDER BY event_ts ROWS UNBOUNDED PRECEDING) AS session_no
  FROM flagged
)
SELECT user_id, session_no,
  MIN(event_ts) AS session_start, MAX(event_ts) AS session_end, COUNT(*) AS event_count
FROM numbered
GROUP BY user_id, session_no
ORDER BY user_id, session_start;

Worked example (executed in DuckDB). User 1 has four events: 09:00, 09:10 (10 min gap, same session), 09:50 (40 min gap from 09:10, new session), 09:55 (5 min gap, same session). User 2 has one event.

user_id | session_no | session_start | session_end | event_count
1       | 1          | 09:00:00      | 09:10:00    | 2
1       | 2          | 09:50:00      | 09:55:00    | 2
2       | 1          | 08:00:00      | 08:00:00    | 1

This matches the design exactly: the 40-minute gap between the second and third event correctly splits user 1 into two sessions, while the 10-minute and 5-minute gaps stay within a session.

Extending the same technique

• Within-session event numbering. Once session_no exists, a second ROW_NUMBER() OVER (PARTITION BY user_id, session_no ORDER BY event_ts) numbers each event's position inside its session, useful for "what was the 3rd action in this session." Executed on the same data, event 1 in session 1 gets event_in_session = 1, event 2 gets 2, and the counter resets to 1 at the start of session 2.
• Longest session / top-3 sessions by duration. Compute session_end - session_start as duration in the aggregated query above, then rank with ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY duration DESC) and filter to <= 3 (this is the same top-N-per-group pattern used elsewhere in this topic, just applied to session duration instead of a raw column).
• Cross-device boundaries. If the same person can generate events on more than one device, sessionizing PARTITION BY user_id alone conflates two genuinely separate concurrent sessions (phone and laptop open at the same time) into one, because ordering by timestamp interleaves both devices' events into a single sequence. Two honest options: keep device_id in the partition key to get one session series per device (loses the "this was really one visit across devices" view), or sessionize per device first and then merge sessions whose time ranges overlap or sit within the gap threshold across devices, which is an interval-merge problem, not a single window-function pass. There is no single-pass window trick that correctly merges cross-device sessions; treat it as a two-stage pipeline.
• Multiple time zones. If event_ts is stored in local time per event (a common mobile-analytics mistake) instead of a single canonical zone like UTC, the 30-minute gap calculation is comparing apples to oranges across a daylight-saving transition or a user who travels between zones mid-session: a 30-minute wall-clock gap can appear as 90 minutes or -30 minutes around a DST shift. Convert every timestamp to UTC (or a single fixed offset) before computing gaps, and keep the local zone only for display.

Trade-offs & pitfalls

• Ties or duplicate timestamps for the same user need a secondary ORDER BY key (an event id) or the LAG-based ordering isn't fully deterministic.
• This pattern requires a full sort per partition (O(n log n)) followed by a single linear pass (O(n)) for the running sum; at very large event volumes the sort dominates, so partitioning/bucketing the source table by user (or by user and day) before running this query matters more than the window logic itself.
• The exact-30-minutes boundary case: > treats an exact 30-minute gap as still the same session; use >= if the intended rule is "a session ends at exactly 30 minutes of inactivity."