1. Window Patterns

The Hook

Six SQL questions that everyone hits in real production work:

1. "Top 3 customers per region by sales."
2. "All orders, with each order's running customer total."
3. "Find user sessions" (group activity into bursts separated by 30+ minutes of silence).
4. "Detect gaps in the timestamp sequence" (where did the data feed go silent?).
5. "Each row's percentage of its country's total."
6. "Rank rows, but reset the rank when a category changes."

Each is a window-function question. Each has a canonical pattern. Once you know the patterns, every "I have rows and I need per-row context" question collapses into "which pattern is this?"

This chapter is the catalogue. The previous four chapters built the vocabulary; this one shows what to say with it. By the end you'll have a mental cookbook of "I've seen this shape before" templates that cover 90% of production analytical SQL.

Table of contents

1. Top-N per group
2. Running total / running average
3. Percent of total
4. Gap detection
5. Sessionisation
6. Gaps and islands
7. Deduplication: "keep the latest"
8. Edge cases and pitfalls
9. Production reality
10. Practice ladder
11. Cross-links
12. Final takeaway

Top-N per group

The most-asked SQL interview question. From Ranking:

The pattern:

1. Rank in a CTE — ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC).
2. Filter outer — WHERE rn <= N.

Variations:

• Top 1 per group ("the most recent record") → WHERE rn = 1.
• Top N with ties allowed → use RANK() instead of ROW_NUMBER.
• Bottom N → reverse the ORDER BY direction.

This pattern replaces correlated subqueries (WHERE x = (SELECT MAX FROM ...)), self-joins on rank conditions, and LATERAL joins for an entire class of question.

Running total

Default-frame ordered window:

For each row, the sum of all this customer's prior orders plus this one.

For a moving average (fixed-width trailing window), use an explicit ROWS BETWEEN N PRECEDING AND CURRENT ROW frame (Frames).

Percent of total

The "share of category" question:

SUM OVER (PARTITION BY c.country) is the per-country total visible to every row. Divide each row's sales by it for the percentage. One pass; per-row detail with per-group context.

Variations:

• Percent of grand total → OVER () (empty window).
• Cumulative percent → OVER (ORDER BY ... ) for the running cumulative numerator.

Gap detection

"Where did the data feed go silent for more than N seconds/minutes/hours?" — LAG plus a comparison.

Two events flagged — the ones starting new "bursts" after silent gaps. This is the building block for sessionisation.

Sessionisation

Group activity into "sessions" — bursts of activity separated by gaps. The standard pattern uses LAG to detect a session boundary, a running SUM to assign session IDs.

Sessionisation: events with a gap from the previous event larger than the threshold start a new session. The yellow events are session boundaries; the green groups are the resulting sessions.

Step by step:

1. new_session is 1 for rows that start a new session (the first row, or any row > 30 min after the previous), 0 otherwise.
2. SUM(new_session) OVER (ORDER BY timestamp_ms) is a running total of those 1s — which gives every row in the same session the same number.

Three sessions. The pattern generalises: any time you can detect "session boundaries" with a LAG comparison, the cumulative-SUM-of-flags trick assigns session IDs.

Gaps and islands

A canonical SQL puzzle: "find consecutive runs of values, with the gaps between them." The trick: ROW_NUMBER minus an "expected" sequence.

Three streaks identified.

The trick: when days are consecutive, day_num - rn is a constant. When there's a gap, the difference jumps. Grouping by day_num - rn collapses each consecutive run into one group.

Output shows three streaks: April 1-3 (3 days), April 7-8 (2 days), April 10 (1 day).

This pattern (or its kin) shows up in any "consecutive streaks" question — login streaks, on-call shifts, alarm-sequence detection.

Deduplication

"Keep the latest version of each record" — top-1 per group with ORDER BY ... DESC:

Each customer's most-recent version. Two rows out, regardless of how many versions exist. The pattern works for any "latest by some criterion" — most recent edit, highest score, top revision.

DISTINCT ON (col1, col2) (Postgres-specific) is a shorthand for the same pattern; not portable but cleaner-looking. Most production code I've seen uses the ROW_NUMBER form for portability.

Edge cases and pitfalls

Tiebreakers, again

For all "top-N" patterns, tied rows in the ORDER BY produce non-deterministic output. Add a tiebreaker:

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sales DESC, order_id ASC)

The primary key is the safest tiebreaker.

Window expressions duplicate

SELECT order_id, sales,
       SUM(sales) OVER (PARTITION BY customer_id) AS total,
       sales * 100.0 / SUM(sales) OVER (PARTITION BY customer_id) AS pct
FROM orders;

SUM(sales) OVER (...) appears twice. Most planners optimise this — the window is computed once and reused. If your dialect doesn't, hoist into a CTE.

The WINDOW clause (standard SQL, supported by Postgres) lets you name a window once and reuse it:

SELECT order_id, sales,
       SUM(sales) OVER w AS total,
       sales * 100.0 / SUM(sales) OVER w AS pct
FROM orders
WINDOW w AS (PARTITION BY customer_id);

Cleaner; less repetition.

Watch for partition explosions

PARTITION BY over a high-cardinality column (e.g., user_id on a billion-user table) creates many small partitions. The window function runs per partition; total cost is manageable when partitions are small. But watch out for queries that compute a window over an entire table without partitioning — the engine has to materialise/sort the whole thing.

Mixed window aggregates and GROUP BY

A GROUP BY query can contain window functions — but the windows operate on the post-grouped result, not the raw rows. Subtle. Usually you want to compute aggregates first (in a CTE), then add windows in the next layer.

Production reality

Codefolio's hello_events is naturally suited to several of these patterns. Two production-realistic queries:

(1) Per-hour stats with a running daily total:

WITH hourly AS (
  SELECT DATE_TRUNC('hour', TO_TIMESTAMP(timestamp_ms / 1000.0)) AS hour,
         COUNT(*) AS events
  FROM hello_events
  WHERE timestamp_ms >= EXTRACT(EPOCH FROM NOW() - INTERVAL '7 days') * 1000
  GROUP BY hour
)
SELECT hour, events,
       SUM(events) OVER (
         PARTITION BY DATE_TRUNC('day', hour)
         ORDER BY hour
       ) AS running_daily_events
FROM hourly
ORDER BY hour;

Aggregate per hour first; then a partitioned running total per day. Each row shows "events this hour" and "cumulative-so-far this day."

(2) Sessionisation against the hello-events log:

The pattern from Sessionisation, applied to real codefolio data. Adjust the gap threshold (30 minutes here) to whatever your "session" semantics demand.

These two queries cover most of what an analytics dashboard around hello_events would need. Memorise the patterns; the queries write themselves.

Practice ladder

1. Top 3 customers per country by score. Hint: ROW_NUMBER + CTE + outer filter.
2. For each customer, their order history with a running total. Hint: SUM(sales) OVER (PARTITION BY customer_id ORDER BY order_date).
3. For each event in hello_events, the gap (in seconds) since the previous event. Hint: LAG(timestamp_ms), then arithmetic.
4. Group hello_events into sessions where each session is a burst of activity separated by ≥ 5-minute gaps. Output: (session_id, start_time, end_time, event_count). Hint: LAG to detect new-session, cumulative SUM of new-session flags to assign session IDs, then GROUP BY session_id.
5. For each customer, their first and last order's order_date and the count of orders. Hint: MIN/MAX/COUNT per customer_id — could be regular GROUP BY or OVER (PARTITION BY customer_id).
6. Find "streaks" of consecutive order_dates per customer. Hint: gaps-and-islands. day_num - ROW_NUMBER() is constant within a streak.
7. Why does this fail?
   SELECT * FROM orders WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sales DESC) <= 3;

   Hint: window functions can't be in WHERE. Wrap in CTE.

Cross-links

• Previous in this module: Value Functions — LAG is the workhorse for sessionisation and gap detection.
• Module complete. Phase 3 (Row Functions + Window Functions) is now fully covered. Next phase: CTEs and Recursion, Schema and Constraints, Indexes and Performance, Transactions and Concurrency, Advanced Patterns.
• Forward reference: CTEs — many of these patterns benefit from naming intermediate windows in CTEs for clarity.
• Forward reference: Indexes and Performance — covering indexes on (partition_col, order_col) make window queries dramatically faster.

Final Takeaway

Your Turn

Before you move on, check your understanding with the coach — explain the idea, apply it, weigh the trade-offs, then defend your reasoning.