Advanced SQL: Window Functions, CTEs, and Subqueries Questions

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."

Direct answer: Filter to failed attempts, group consecutive failures for each user into a run using a running count of successes (each success starts a new run of failures), number the failures within each run, and then check whether the timestamp 3 positions apart in the same run is within 10 minutes of the first. This avoids both false positives (isolated failures with successes or long gaps between them break the run before they can combine into 3) and duplicate alerts, because it flags a single deterministic window_start per qualifying triple rather than every possible sliding window over the same failures.

Structured elaboration

1. Group consecutive failures. A running SUM of a success indicator (1 for success, 0 for failure), ordered by time, increments only when a login succeeds. Between two successes, that running total is constant, so it acts as a group id: every failure that happens between the same pair of successes (or before the first success) shares one group id. This is the identical gaps-and-islands mechanism used to find consecutive-day streaks elsewhere in this topic, applied here to failure runs instead of calendar days.
2. Number failures within each run. ROW_NUMBER() partitioned by (user, group id) gives each failure its position in its run: 1st, 2nd, 3rd failure since the last success.
3. Check the 10-minute window on a triple. Self-join the numbered failures to themselves, matching row N to row N+2 in the same run, and keep pairs where the timestamp gap is at most 10 minutes. The earlier row's timestamp is the offending sequence's window_start.

WITH ordered AS (
  SELECT user_id, attempted_at, success,
    SUM(CASE WHEN success THEN 1 ELSE 0 END) OVER (
      PARTITION BY user_id ORDER BY attempted_at ROWS UNBOUNDED PRECEDING
    ) AS success_grp
  FROM logins
),
fails AS (
  SELECT user_id, attempted_at, success_grp,
    ROW_NUMBER() OVER (PARTITION BY user_id, success_grp ORDER BY attempted_at) AS fail_idx
  FROM ordered
  WHERE success = FALSE
)
SELECT DISTINCT n1.user_id, n1.attempted_at AS window_start
FROM fails n1
JOIN fails n3
  ON n1.user_id = n3.user_id
 AND n1.success_grp = n3.success_grp
 AND n3.fail_idx = n1.fail_idx + 2
 AND n3.attempted_at - n1.attempted_at <= INTERVAL '10 minutes'
ORDER BY user_id, window_start;

Worked example (executed in DuckDB). User 1: 3 failures at 09:00, 09:02, 09:04 (all within 4 minutes, then a success), plus an isolated failure at 09:30. User 2: 3 failures at 10:00, 10:20, 10:40 (20 minutes apart each, no success in between, so all one run, but spanning too much time).

flagged_user_id | window_start
1                | 09:00:00

Only user 1 is flagged. User 2's three failures are genuinely consecutive (same run, no intervening success) but span 40 minutes end to end, so the 3rd-failure-minus-1st-failure gap (40 minutes) exceeds 10 minutes and correctly does not trigger.

How this avoids false positives. An isolated failure surrounded by successes, or spread far from other failures in time, never gets two more failures within the same success_grp inside a 10-minute span, so it never satisfies the fail_idx = fail_idx + 2 self-join condition. The grouping by success_grp is what prevents counting a failure from a completely different, much earlier attack attempt toward the current count: any success in between resets the run.

How this avoids duplicate alerts for overlapping sequences. A naive rolling-window formulation (check every failure against every failure exactly 10 minutes later) would fire once per qualifying failure inside a longer run of 4+ failures, producing overlapping, redundant alerts for what is really one incident. This query instead reports exactly one window_start per distinct triple (fail_idx, fail_idx+2): a run of exactly 3 failures produces exactly one alert; a run of 5 rapid failures produces up to 3 overlapping-but-distinct triples (1-3, 2-4, 3-5), which is a deliberate design choice (each triple is separately true), not a bug, but if the requirement is 'one alert per incident, not one per triple,' take MIN(window_start) per (user_id, success_grp) after this query to collapse a whole run down to a single alert.

Trade-offs & pitfalls

• This detects 3+ consecutive failures within a 10-minute span of THAT triple; it does not detect, say, 2 failures 8 minutes apart followed by a 3rd failure 15 minutes after the first two but still within 10 minutes of the 2nd. If the intended semantics is 'any 3 failures within a rolling 10-minute window regardless of adjacency,' that's a genuinely different, more expensive query (a self-join or a true sliding window count, not a fixed offset of 2).
• Collapsing a long run of failures down to one alert (via MIN(window_start) GROUP BY user_id, success_grp) is a deliberate design decision, not automatic; decide upfront whether security wants one alert per run or one per qualifying triple.
• The self-join is the expensive part at scale: it's proportional to the number of failure pairs 2 apart within a run, which is cheap for normal traffic but can grow for a user under sustained attack with thousands of failures in one run; a streaming engine with a native session/window operator is usually a better fit for production-scale abuse detection than a batch self-join.

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.

Direct answer: When two rows tie on every column in your ORDER BY, including the timestamp, ROW_NUMBER() = 1 no longer has a single correct answer: which of the tied rows gets rank 1 depends on the engine's internal row order, which can differ across runs, across a re-execution after a vacuum/reorganize, or across a query plan change, none of which are things you control. The fix is to keep adding ORDER BY keys until no two rows in any partition can tie on all of them simultaneously, ending with a column that is guaranteed unique (a primary key or surrogate id) as the final, unconditional tiebreaker.

Structured elaboration

Why it breaks determinism. ROW_NUMBER() assigns 1, 2, 3, ... to rows within a partition based on the ORDER BY clause. SQL only guarantees a well-defined order among rows that the ORDER BY clause can actually distinguish; rows that tie on every listed key are, per the standard, in an implementation-defined order relative to each other. Two runs of the identical query, or the same query before and after a table reorganization, can legally return a different row as rank 1 even though nothing about the data changed. For a dedup pipeline, that means the 'surviving' row for a given key can flap between runs, which breaks any downstream process relying on a stable choice (a slowly changing dimension load, an incremental pipeline keyed on the surviving row's id, or just a report that should be reproducible).

A concrete multi-criteria tie-break. A common real fix, not just 'add the id': when deduplicating events that arrived from multiple devices for the same user at the identical timestamp, rank by business priority first, THEN by recency, THEN by id: prefer a desktop-sourced event over mobile, and mobile over tablet, before falling back to timestamp, and only fall back to the row id if literally everything else ties.

WITH ranked AS (
  SELECT id, user_id, score, event_ts, device,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY score DESC, event_ts DESC,
        CASE device WHEN 'desktop' THEN 1 WHEN 'mobile' THEN 2 WHEN 'tablet' THEN 3 ELSE 4 END,
        id
    ) AS rn
  FROM events2
)
SELECT * FROM ranked WHERE rn = 1;

Worked example (executed in DuckDB). Two rows for user 1 tie exactly on score (90) and event_ts (09:00:00): one from mobile (id 2... shown as id in output), one from desktop. A third row for the same user has a lower score.

-- ORDER BY score DESC, event_ts DESC only (no further tiebreak):
id | score | event_ts | device  | rn
1  | 90    | 09:00:00 | mobile  | 1   <- arbitrary: this engine happened to rank id=1 first
2  | 90    | 09:00:00 | desktop | 2
3  | 80    | 08:00:00 | tablet  | 3

-- with device-priority + id added as final tiebreak:
id | score | event_ts | device  | rn
2  | 90    | 09:00:00 | desktop | 1   <- deterministic: desktop always wins this tie now

Adding the device-priority CASE and the id column changes which row wins the tie from 'whatever the engine happens to return first' to a fixed, explainable, always-reproducible answer: desktop over mobile over tablet, with the surrogate id as the final fallback if even device somehow ties.

DISTINCT ON as an alternative (Postgres and DuckDB). DISTINCT ON expresses the identical intent more compactly on engines that support it: pick one row per key, ordered by the same tiebreak chain, without a separate ROW_NUMBER/filter step.

SELECT DISTINCT ON (user_id) id, user_id, score, event_ts, device
FROM events2
ORDER BY user_id, score DESC, event_ts DESC,
  CASE device WHEN 'desktop' THEN 1 WHEN 'mobile' THEN 2 WHEN 'tablet' THEN 3 ELSE 4 END, id;

Run against the same data, this returns the identical winning row (desktop, id matching the ROW_NUMBER = 1 result), confirming the two formulations agree once the ORDER BY chain is fully deterministic. DISTINCT ON is not portable (Postgres and DuckDB support it; SQL Server, Snowflake, and BigQuery do not), so ROW_NUMBER() = 1 remains the more broadly applicable pattern.

A related but distinct angle: time-window duplicates instead of exact-key duplicates. Not every 'duplicate' shares an identical key. If two events for the same user within, say, 5 seconds of each other should be treated as one duplicate click even though their timestamps differ slightly, exact-key PARTITION BY doesn't apply at all; that's the gaps-and-islands technique instead (bucket by a rounded/truncated timestamp, or flag a new group whenever the gap from the previous event exceeds the tolerance), not a tie-break question. Worth naming explicitly: 'duplicate' meaning exact-key collision and 'duplicate' meaning near-in-time collision are two different problems that happen to both get called deduplication.

Trade-offs & pitfalls

• A hash of the full row's content is another valid final tiebreaker when there's no natural priority order, but it must be computed over stable, deterministic functions only; never include random(), now(), or any non-deterministic expression in a tiebreak chain, or you reintroduce the exact nondeterminism you were trying to remove.
• Document the tiebreak precedence somewhere visible (a comment in the query or a data-quality doc); a reviewer six months later has no way to infer 'why does desktop always win' from the CASE expression alone.
• Adding more ORDER BY keys costs a marginally more expensive sort, not more scans; it's essentially free compared to the cost of nondeterministic output in a pipeline anyone depends on.

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.