Advanced SQL and Window Functions

Data Engineering & Modern Data Platforms · Lesson 07 · Data Engineering Foundations

Lesson Overview

TechLearningPro's growth team asked a question that a GROUP BY cannot answer honestly: of the learners who opened Data Engineering Foundations in October, how many reached checkout, how many paid, how many finished lesson 1, and how long did each step take for the learners who continued? A grouped query can count events. It cannot, in the same row as the original event, tell you the previous event, the running total, the rank inside a cohort, or whether this completion is the first in a session. Those questions keep every input row and add a calculation that looks across a peer set. That calculation is a window function.

This lesson is the analytical layer on top of SQL Fundamentals for Data Engineers. You already filter, join, aggregate, and compose queries with subqueries and common table expressions. Here you learn the OVER clause as a contract: partition the rows, order them, choose a frame, compute a value, and leave the row count unchanged. You will use that contract for ranking, running totals, moving averages, gaps between events, sessionization, ordered funnels, and cohort retention. You will also learn the failure mode that produces a confident wrong dashboard: filtering a window result in WHERE, using the default frame with LAST_VALUE, or treating a tied timestamp as a unique sequence.

The SQL is written for PostgreSQL, which is TechLearningPro's system of record and the engine documented in the PostgreSQL course. The window syntax itself is core SQL and behaves the same way in Spark SQL, DuckDB, and the major warehouses, with one important exception. Snowflake, BigQuery, and Databricks SQL accept a QUALIFY clause that filters on a window result. PostgreSQL does not. This lesson shows the portable pattern: compute the window in an inner query or CTE, then filter in an outer query. That pattern is what an ETL job can run in PostgreSQL today and what a warehouse model can keep when Module 2 moves the same logic downstream.

By the end of the lesson you should be able to read a progress-funnel query the way you read a pipeline: name the grain of each CTE, name the sort the engine must perform, and say what a tie, a null, or a replayed event does to the number.

Learning Objectives

By the end of this lesson, you should be able to:

1. Explain why a window function preserves input rows while GROUP BY collapses them, and choose the construct that matches the grain of the metric.
2. Read an OVER clause as three independent decisions: PARTITION BY, ORDER BY, and the frame (ROWS, RANGE, or GROUPS).
3. Predict the default frame PostgreSQL uses when ORDER BY is present, and write an explicit frame when the default is the wrong peer set.
4. Choose ROW_NUMBER, RANK, DENSE_RANK, and NTILE for a stated tie-breaking rule, and add a unique key to ORDER BY when the rank must be deterministic.
5. Use LAG and LEAD to measure gaps, detect session boundaries, and compare a payment with the previous payment for the same learner.
6. Write running sums and interval moving averages, including a RANGE frame on timestamp values with an INTERVAL bound.
7. Explain why LAST_VALUE with the default frame returns the current row, and write the frame that returns the last peer in the partition.
8. State the logical order of WHERE, GROUP BY, HAVING, window evaluation, SELECT, and ORDER BY, and explain why a window alias is invisible to WHERE.
9. Implement a QUALIFY-style filter with a subquery or CTE that runs on PostgreSQL and remains valid in Spark SQL.
10. Deduplicate a replayed event stream with ROW_NUMBER and, on PostgreSQL, compare that pattern with DISTINCT ON.
11. Build an ordered progress funnel and a weekly cohort-retention query whose intermediate grains are explicit.
12. Sessionize a learner event stream with a gap threshold, and explain how peer rows in a RANGE frame can assign two events the same session sequence.
13. Read a window query as a sort and a partition: estimate memory pressure, name an index that can supply order, and decide when a plain aggregate is cheaper.
14. Review a dashboard query for null ordering, unstable ties, double-counted learners, and frames that silently include the wrong days.
15. Hand the same funnel definition to Module 2 as a transformation contract: grain, steps, time bounds, and the rule for late or replayed events.

Prerequisites

• SQL Fundamentals for Data Engineers: SELECT, JOIN, GROUP BY, HAVING, subqueries, and CTEs. This lesson does not re-teach joins. It uses them as the input to a window.
• Enough PostgreSQL to run a query in psql or a notebook and to read a table definition. Engine-specific syntax for windows, ranking, and LEAD / LAG is taught in Window Functions, Ranking Functions, and LEAD & LAG. The parallel SQL-course page is Window Functions. Use those pages when you want the function catalog. This lesson is about designing metrics.
• A mental model of TechLearningPro's operational tables: learners, courses, enrollments, payments, and append-only learning events. Lesson 9 will model them formally. Here they are the source of the examples.
• Comfort with timestamp with time zone and with the fact that two events can share a timestamp. Ties are the main source of non-deterministic window results.

You do not need a warehouse account. Every query in the walkthrough is PostgreSQL. Where a cloud engine adds syntax, the lesson names the engine and then shows the form that still runs here.

Detailed Core Concepts

The row a window keeps

An aggregate function without OVER is a request to collapse. COUNT(*) over a filtered set returns one row. GROUP BY learner_id returns one row per learner. The input rows are gone. Anything you still need from those rows must be stuffed into another aggregate, a join back to the detail, or a different query.

A window function is a request to annotate. The engine still groups rows into partitions, and it still orders them, but the result has one output row for each input row. SUM(amount_cents) OVER (PARTITION BY learner_id) attaches the learner's total to every payment row of that learner. The payments remain. That is why window functions are the right tool for "this row, in the context of its peers" and the wrong tool for "one number per group, and I do not need the detail."

TechLearningPro hits both shapes in the same product. Finance wants one revenue number per course per day: that is GROUP BY. The learner progress page wants each lesson completion plus "this is completion 6 of 42 in the course" and "the previous lesson was finished 18 hours ago": that is a window. The cohort chart wants both: a window or a first-event aggregate to place the learner in a cohort, then a GROUP BY to count the cohort. Senior engineers separate those grains into CTEs so a reviewer can point at the CTE that is still at event grain and the CTE that has collapsed to learner grain.

The OVER clause is three decisions

sqlCopy
function_name(arguments) OVER (
  PARTITION BY partition_expression [, ...]
  ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]
  frame_clause
)

PARTITION BY defines the peer group. Omit it and the peer group is the whole result of the FROM / WHERE / GROUP BY / HAVING stage. For a learner timeline, the partition is almost always learner_id, or (learner_id, course_id) when the course is the unit of progress. Partitioning by a high-cardinality key such as event_id is a design error: every partition has one row, and the window tells you nothing about peers.

ORDER BY inside OVER defines sequence inside the partition. It is not the query's final ORDER BY. A query can compute LAG(event_time) OVER (PARTITION BY learner_id ORDER BY event_time) and then present the result ORDER BY course_id. The window order and the presentation order answer different questions. If the window order is not unique, peers exist. PostgreSQL's RANGE frame treats peers as a single step. ROW_NUMBER still emits distinct numbers, but which tied row gets 1 is not stable unless you extend ORDER BY with a unique column such as event_id.

The frame defines which of the ordered peers are visible to this row's calculation. Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, CUME_DIST, PERCENT_RANK) use the order and do not take a frame. Aggregate functions used as windows (SUM, AVG, COUNT, MIN, MAX) use a frame. Mixing those two families in one OVER is normal. Giving an aggregate the wrong frame is the bug that ships.

Default frames

PostgreSQL's rule, which matches the SQL standard, is precise enough to memorize.

When the window has an ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. CURRENT ROW in a RANGE frame means the current row and every peer with the same ORDER BY value. A running sum of daily revenue ordered only by sale_date therefore includes every row that shares today's date, not "rows up to this physical row."

When the window has no ORDER BY, there is no sequence. The frame is the entire partition. SUM(amount_cents) OVER (PARTITION BY learner_id) is the learner total on every row. That query is valid. It is also a sort-free partition aggregate, which is cheaper than an ordered running sum and is the right shape when you truly want the whole partition.

Write the frame when a reader must not guess. The extra line is the specification.

sqlCopy
SUM(amount_cents) OVER (
  PARTITION BY learner_id
  ORDER BY paid_at, payment_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_amount_cents

ROWS counts physical rows in the ordered window. RANGE counts values of the ORDER BY expression. GROUPS counts peer groups, which is the SQL:2011 behavior PostgreSQL has provided since version 11. Use ROWS for "the previous 6 events." Use RANGE for "the previous 6 days," because a day with three payments and a day with one payment must not change the width of a time window.

Ranking and ties

Take three lesson scores for one course: 90, 90, and 80.

ROW_NUMBER is the deduplication tool. "Keep the latest payment attempt per order" is ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY attempted_at DESC, attempt_id DESC) = 1 in an outer query. RANK is the leaderboard tool, where two learners at the same score share a place. DENSE_RANK is the tool for "how many distinct scores are better than this one." NTILE splits a partition into n buckets by position, not by value. A cohort of three learners in NTILE(10) does not produce ten meaningful deciles. State the minimum partition size before you publish an NTILE chart.

Nulls sort according to NULLS FIRST or NULLS LAST. PostgreSQL's default for ASC is NULLS LAST, and for DESC is NULLS FIRST. A "latest non-null completion" query that orders completed_at DESC will put a null completion ahead of every real timestamp. If null means "not completed," filter those rows before the window or declare NULLS LAST on the descending key.

Offsets, frames, and the LAST_VALUE trap

LAG(expr, offset, default) reads a row earlier in the ordered partition. LEAD reads a later row. The offset is a row count, not a time distance. LAG(event_time, 1) is the previous event even if it was 12 days ago. A time-aware previous event is a RANGE frame or a lateral join, not a larger LAG offset. The third argument replaces the null that appears on the first row of a partition. Leaving it null is often what you want, because "there is no previous event" and "the previous event was at a real timestamp" must stay distinct. Coalescing that null to the current timestamp invents a zero-length gap and hides new learners.

FIRST_VALUE and LAST_VALUE are aggregates over the frame, not "first and last in the partition" by magic. With the default frame, LAST_VALUE(lesson_slug) sees only rows up through the current peer. On the first row of the partition it returns the first row's value and looks like a bug. The frame that matches the English phrase "last in the partition" is:

sqlCopy
LAST_VALUE(lesson_slug) OVER (
  PARTITION BY learner_id, course_id
  ORDER BY completed_at, lesson_slug
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE(expr, n) has the same frame dependence. If you need a value from a specific row and the frame keeps confusing reviewers, compute ROW_NUMBER in a CTE and join back. Clarity beats a clever frame in a metric other people will edit.

EXCLUDE options, available in PostgreSQL, remove the current row, its peers, or its group from the frame: EXCLUDE CURRENT ROW, EXCLUDE TIES, EXCLUDE GROUP. A "average of the other learners in this cohort" calculation needs EXCLUDE CURRENT ROW. Without it the learner's own score is inside the average that is supposed to describe everyone else.

Where the window sits in the query

Logical processing order for the parts this lesson uses:

1. FROM and JOIN produce the row set.
2. WHERE filters input rows. Window functions have not run.
3. GROUP BY and HAVING collapse that set. A window in the same SELECT as a grouped query runs on the grouped rows.
4. Window functions evaluate. Their aliases do not exist yet for WHERE.
5. SELECT list expressions, including the window calls, produce output columns.
6. DISTINCT, ORDER BY, and LIMIT / OFFSET present the result.

Two consequences follow. You cannot write WHERE row_number = 1 if row_number is a window in the same SELECT. The filter has to wrap the window:

sqlCopy
SELECT order_id, attempted_at, status
FROM (
  SELECT
    order_id,
    attempted_at,
    status,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY attempted_at DESC, attempt_id DESC
    ) AS attempt_rank
  FROM billing.payment_attempts
) ranked
WHERE attempt_rank = 1;

You also cannot nest a window call inside another window call in the same SELECT. "Rank learners by their running total" is two layers: a CTE that computes the running total, and an outer query that ranks it. That restriction is a feature for reviewers. Each layer has one grain and one reason to exist.

PostgreSQL lets the final ORDER BY use a SELECT alias, so ORDER BY attempt_rank works at the outer presentation step. That does not move the filter earlier.

QUALIFY, and the pattern you will actually ship

QUALIFY is a filter that runs after window functions and before the query's presentation ORDER BY. Snowflake, Google BigQuery, and Databricks SQL implement it. The query reads well:

sqlCopy
-- Valid in Snowflake, BigQuery, and Databricks SQL.
-- Not valid in PostgreSQL.
SELECT order_id, attempted_at, status
FROM billing.payment_attempts
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY order_id
  ORDER BY attempted_at DESC, attempt_id DESC
) = 1;

PostgreSQL will reject QUALIFY as a syntax error. Spark SQL, depending on version and dialect mode, may also reject it. TechLearningPro's operational database is PostgreSQL, and the Module 2 batch jobs start from SQL that must run there before anyone ports it. The subquery above is the portable QUALIFY. A named CTE is the same pattern and is easier to test:

sqlCopy
WITH ranked_attempts AS (
  SELECT
    order_id,
    attempted_at,
    status,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY attempted_at DESC, attempt_id DESC
    ) AS attempt_rank
  FROM billing.payment_attempts
)
SELECT order_id, attempted_at, status
FROM ranked_attempts
WHERE attempt_rank = 1;

When a warehouse model later adopts QUALIFY, the CTE is the specification you are compressing. Keep the CTE in code review until the dialect decision is explicit in the model file. Do not fork the dedupe rule.

PostgreSQL also offers DISTINCT ON (order_id), which keeps the first row of each order_id after the query ORDER BY. It is shorter for "latest row per key" and it is not standard SQL. Use it inside PostgreSQL services when the team agrees. Use the ROW_NUMBER CTE when the SQL must also run in Spark or a warehouse. Lesson 8's EXPLAIN discussion is where you compare their plans on a real table. The semantic rule is the same only when the ORDER BY of DISTINCT ON starts with the DISTINCT ON expressions and then the tie-break you mean.

Funnels are ordered existence, not a pile of flags

A progress funnel asks a stricter question than "did this event name ever appear?" TechLearningPro's acquisition funnel is ordered: course view, then checkout started, then payment succeeded, then lesson completed. A learner who receives a support-granted enrollment has a lesson_completed event and no payment_succeeded. A flag-style funnel counts that learner as a completer who never paid, and the conversion rate from payment to completion exceeds 100 percent or, worse, someone "fixes" it by hiding the row. An ordered funnel records the first timestamp of each step and counts the learner at step n only when that timestamp exists and is greater than or equal to step n−1.

Equal timestamps are allowed when two events are emitted in one transaction and share transaction_timestamp(). The business rule belongs in the metric definition: either "same timestamp counts as ordered" or "the step requires a strictly later timestamp." Pick one and test it. Replayed events are handled before the funnel, by the dedupe CTE, so the first timestamp is the first real occurrence and not the first duplicate the pipeline happened to load.

Cohort retention is a different grain. The cohort is the week of enrollment. Activity is a later week in which the learner completed a lesson. The report is (cohort_week, weeks_since_enrollment) → active_learners / cohort_size. Window functions help locate the cohort assignment and the first activity. The published chart is an aggregate. Mixing those in one SELECT is how a distinct-count window accidentally counts groups instead of learners. The walkthrough keeps cohort size in its own CTE.

Sessionization is a gap, then a running sum of gap flags

A learning session is not a column the application must send. It is a derived key: start a new session when the gap since the previous event for this learner exceeds a threshold, commonly 30 minutes for interactive learning. The pattern has two windows and they must not be collapsed into one.

The first window is LAG(event_time). The comparison event_time - previous > interval '30 minutes' yields a 0 or 1 flag. The first event in a partition has a null lag, and that null is a new session. The second window is a running SUM of the flag, which becomes session_seq inside the learner. Concatenate learner_id and session_seq and you have a session key that is stable as long as history is stable.

That last sentence is the operational constraint. If a late event arrives with an earlier timestamp, every subsequent session_seq for that learner can change. Session keys derived from a running sum are not immutable business keys. They are rebuildable attributes. A nightly batch that recomputes sessions from the full event history is safe. A streaming job that emits session_seq as soon as the event arrives will rewrite history when the late event shows up. Module 2's batch pipelines can recompute. A streaming design has to choose watermarks and late-data policy, which is a later module. This lesson's contract is: sessions are derived, ordered by (event_time, event_id), and recomputed from retained events.

What the engine does, in cost terms

An ordered window is a sort unless an index or an earlier sort already delivered rows in partition-and-order sequence. PostgreSQL's planner can use an index on (learner_id, event_time, event_id) to avoid an explicit sort for a window partitioned by learner_id and ordered by event_time, event_id. It will not use an index on event_time alone to satisfy a per-learner order. After the sort, the executor walks the partition and maintains the frame. A frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for SUM can be maintained incrementally. A moving frame that slides both edges, or a RANGE frame on timestamps, keeps more state.

work_mem bounds sort and hash memory per operation, not per query. A dashboard that runs four ordered windows over a year of events can spill four sorts to disk. The spill is correct and slow. The fix is a narrower date predicate, a pre-aggregated daily table when the grain allows it, or a scheduled result table. It is not a hint that window functions are unsuitable for analytics. It is a hint that the window should see the grain you actually rank.

Window functions do not replace a join. "Attach the course title" is a join to courses. "Attach the previous event time" is a window. Using LAG across a join that multiplied rows will compute the previous joined row, which may be a duplicate dimension row rather than the previous event. Aggregate or dedupe to the intended grain, then open the window.

Architecture and Design Diagrams

The metric this lesson builds is a batch SQL model. It reads operational tables, derives event grain, learner grain, and cohort grain, and publishes a small result set for the progress dashboard. Module 2 will put this SQL inside a scheduled transformation. The diagram shows the grains so a reviewer can see where rows are allowed to collapse.

mermaidCopy
flowchart TD
  subgraph sources [Operational tables in PostgreSQL]
    EV[learning.events append-only]
    EN[learning.enrollments]
    PA[billing.payment_attempts]
  end

  subgraph event_grain [Event grain]
    DEDUPE["Dedupe CTE: ROW_NUMBER per event_id keep 1"]
    SESS["Session CTE: LAG gap then running SUM"]
  end

  subgraph learner_grain [Learner and step grain]
    FIRST["First-touch timestamps per funnel step"]
    ORDERED["Ordered step flags: step n time >= step n-1"]
  end

  subgraph cohort_grain [Cohort grain]
    COH[Cohort week from enrollment]
    ACT[Active week from lesson_completed]
    RET["Retention: active_learners / cohort_size"]
  end

  DASH[Progress dashboard tables]

  EV --> DEDUPE
  DEDUPE --> SESS
  DEDUPE --> FIRST
  PA --> FIRST
  FIRST --> ORDERED
  EN --> COH
  DEDUPE --> ACT
  COH --> RET
  ACT --> RET
  ORDERED --> DASH
  SESS --> DASH
  RET --> DASH

A single learner moving through one course looks like this. The window annotations sit beside the events. They are not new business events.

mermaidCopy
sequenceDiagram
  participant App as Learning application
  participant DB as PostgreSQL events
  participant Q as Funnel query
  participant Dash as Progress dashboard

  App->>DB: course_view at 10:00
  App->>DB: checkout_started at 10:04
  App->>DB: payment_succeeded at 10:05
  App->>DB: lesson_completed at 11:20
  Note over DB: Replay of payment_succeeded arrives again
  Q->>DB: Dedupe by event_id, then first timestamp per step
  Q->>Q: Keep payment only if its time is at or after checkout
  Q->>Dash: One learner row: viewed, checked out, paid, completed lesson 1

The frame decision is a data-flow decision. ROWS walks a count of events. RANGE walks a width of the order key. For a seven-day revenue average the order key is the sale timestamp and the width is an interval.

mermaidCopy
flowchart LR
  subgraph rangeFrame [RANGE frame for one sale on day D]
    D6["D-6 days"] --> D5[D-5]
    D5 --> D4[D-4]
    D4 --> D3[D-3]
    D3 --> D2[D-2]
    D2 --> D1[D-1]
    D1 --> D0["Day D including peer sales"]
  end

Technical Explanation

Sample schema

The examples use a schema a data engineer can create in a local PostgreSQL. Types are deliberate. Money is bigint cents. Timestamps are timestamptz. Event names are text constrained to a known set once the contract is stable; during exploration they stay text so a new product event does not break the load. The primary key on events is the producer event id, which makes a replay a primary-key conflict rather than a second analytical fact.

sqlCopy
CREATE SCHEMA IF NOT EXISTS learning;
CREATE SCHEMA IF NOT EXISTS billing;

CREATE TABLE learning.events (
  event_id       uuid PRIMARY KEY,
  learner_id     uuid NOT NULL,
  course_id      uuid,
  lesson_slug    text,
  event_name     text NOT NULL,
  event_time     timestamptz NOT NULL,
  ingested_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE learning.enrollments (
  enrollment_id  uuid PRIMARY KEY,
  learner_id     uuid NOT NULL,
  course_id      uuid NOT NULL,
  enrolled_at    timestamptz NOT NULL,
  status         text NOT NULL
);

CREATE TABLE billing.payment_attempts (
  attempt_id     uuid PRIMARY KEY,
  order_id       uuid NOT NULL,
  learner_id     uuid NOT NULL,
  course_id      uuid NOT NULL,
  amount_cents   bigint NOT NULL,
  status         text NOT NULL,
  attempted_at   timestamptz NOT NULL
);

CREATE INDEX events_learner_time_idx
  ON learning.events (learner_id, event_time, event_id);

The index matches the window that defines a learner timeline. It does not make every window free. A window partitioned by course_id needs a different order. Lesson 8 explains how the planner chooses between this index and a sort, and why a bloated index stops being a gift.

Running money, with peers made visible

Assume three successful payments for one learner on the same paid_at date, loaded as attempts that reached succeeded. A RANGE running sum ordered only by paid_at assigns each of those three rows the sum of all three, because they are peers. A ROWS running sum ordered by paid_at, attempt_id assigns a cumulative sum that grows one payment at a time. Both are legal. Finance's "end of day balance" can use RANGE and then take DISTINCT on the day. The learner receipt page that says "after this payment your lifetime total was" needs ROWS plus a unique tie-break, or the receipt depends on the executor's whim.

sqlCopy
SELECT
  attempt_id,
  paid_at,
  amount_cents,
  SUM(amount_cents) OVER (
    PARTITION BY learner_id
    ORDER BY paid_at
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS range_running_cents,
  SUM(amount_cents) OVER (
    PARTITION BY learner_id
    ORDER BY paid_at, attempt_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS rows_running_cents
FROM billing.payment_attempts
WHERE status = 'succeeded';

If paid_at is a timestamptz and two attempts differ by milliseconds, they are not peers under RANGE. The peer bug appears when the column is a date, or when the ORDER BY truncates time with date_trunc inside the window. Truncating for presentation belongs in the SELECT list. Truncating inside ORDER BY changes the frame.

A seven-day moving average that means seven days

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is seven rows. On a daily grain table with no gaps, seven rows are seven days. On a raw payments table, seven rows might be seven minutes of a launch-day spike. Build the daily grain first, then apply the frame, or apply a RANGE interval directly to timestamps.

PostgreSQL accepts an interval bound in a RANGE frame when the ORDER BY expression is a date or timestamp.

sqlCopy
SELECT
  course_id,
  sale_day,
  revenue_cents,
  AVG(revenue_cents) OVER (
    PARTITION BY course_id
    ORDER BY sale_day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS trailing_7d_avg_cents
FROM billing.course_revenue_by_day;

Days with no sales are absent from that average unless the daily table contains explicit zero rows. A trailing average that should treat silence as zero needs a generated date series joined to revenue before the window. That join is a data decision: a course that has not launched should not show a zero average for the year before launch. Bound the series from the course's first sale or from its publish date, and document which one.

Deterministic dedupe of replays

Producers retry. The collector writes the same event_id once if the primary key holds. Analytics still sees duplicates when the producer bug assigned a new event_id to a logical retry, or when two pipelines landed the same upstream id into two columns. The dedupe key has to be the business idempotency key, not whatever column happens to be unique in the warehouse copy.

sqlCopy
WITH ranked AS (
  SELECT
    event_id,
    learner_id,
    course_id,
    lesson_slug,
    event_name,
    event_time,
    ingested_at,
    ROW_NUMBER() OVER (
      PARTITION BY event_id
      ORDER BY ingested_at DESC, event_id
    ) AS rn
  FROM learning.events
)
SELECT event_id, learner_id, course_id, lesson_slug, event_name, event_time
FROM ranked
WHERE rn = 1;

PARTITION BY event_id with a primary key already in force returns one row per event and the window is wasted work. The interesting partition is the business key: (learner_id, course_id, lesson_slug, event_name, event_time) when the product definition of a duplicate is "the same completion recorded twice." Get that definition from the producer owner. A window cannot invent it.

DISTINCT ON is the PostgreSQL-shaped version of the same rule:

sqlCopy
SELECT DISTINCT ON (event_id)
  event_id, learner_id, course_id, event_name, event_time, ingested_at
FROM learning.events
ORDER BY event_id, ingested_at DESC;

The ORDER BY must lead with the DISTINCT ON expressions. PostgreSQL enforces that. What it does not enforce is that you meant ingested_at DESC. An ORDER BY event_id alone keeps an arbitrary row among duplicates. That query parses. It is not a dedupe policy.

Ordered funnel

The step list for the October progress funnel is a contract, not a decoration:

Support-granted enrollments never enter step 1 through this event list. They belong in a separate "invited or comped" funnel so they do not punish the paid conversion rate. Excluding them is a filter on enrollment source, applied before the step timestamps are computed.

sqlCopy
WITH deduped AS (
  SELECT learner_id, course_id, event_name, event_time
  FROM (
    SELECT
      learner_id,
      course_id,
      event_name,
      event_time,
      ROW_NUMBER() OVER (
        PARTITION BY event_id
        ORDER BY ingested_at
      ) AS rn
    FROM learning.events
    WHERE event_time >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
      AND event_time <  TIMESTAMPTZ '2026-11-01 00:00:00+00'
  ) e
  WHERE rn = 1
),
first_touch AS (
  SELECT
    learner_id,
    course_id,
    MIN(event_time) FILTER (WHERE event_name = 'course_view')       AS viewed_at,
    MIN(event_time) FILTER (WHERE event_name = 'checkout_started')  AS checkout_at,
    MIN(event_time) FILTER (WHERE event_name = 'payment_succeeded') AS paid_at,
    MIN(event_time) FILTER (WHERE event_name = 'lesson_completed')  AS completed_at
  FROM deduped
  GROUP BY learner_id, course_id
),
ordered AS (
  SELECT
    learner_id,
    course_id,
    viewed_at,
    (checkout_at  IS NOT NULL AND viewed_at IS NOT NULL AND checkout_at  >= viewed_at)  AS reached_checkout,
    (paid_at      IS NOT NULL AND checkout_at IS NOT NULL AND paid_at    >= checkout_at
       AND checkout_at >= viewed_at) AS reached_payment,
    (completed_at IS NOT NULL AND paid_at IS NOT NULL AND completed_at >= paid_at
       AND paid_at >= checkout_at AND checkout_at >= viewed_at) AS reached_completion
  FROM first_touch
  WHERE viewed_at IS NOT NULL
)
SELECT
  course_id,
  COUNT(*) FILTER (WHERE viewed_at IS NOT NULL) AS viewers,
  COUNT(*) FILTER (WHERE reached_checkout)      AS checkouts,
  COUNT(*) FILTER (WHERE reached_payment)       AS payers,
  COUNT(*) FILTER (WHERE reached_completion)    AS completers
FROM ordered
GROUP BY course_id
ORDER BY course_id;

MIN(...) FILTER (WHERE ...) is a grouped aggregate, not a window. It is the right tool once the grain becomes "first time this learner did this step in this course." The window's job was the dedupe one layer earlier. A design that uses windows for everything is harder to read and often sorts data that only needed a hash aggregate. Use the cheaper operator at the grain that allows it.

Time spent between steps, for the learners who reached both, is percentile_cont(0.5) WITHIN GROUP (ORDER BY checkout_at - viewed_at). percentile_cont is an ordered-set aggregate, not a window, unless you add OVER. The dashboard wants one median per course, so the ordered-set aggregate is the right shape. Putting OVER () on it would repeat that median on every row, which is useful in a detail export and noise in a chart.

Cohort retention without a distinct-count trap

sqlCopy
WITH cohorts AS (
  SELECT
    learner_id,
    date_trunc('week', enrolled_at)::date AS cohort_week
  FROM learning.enrollments
  WHERE status IN ('active', 'completed')
),
cohort_size AS (
  SELECT cohort_week, COUNT(*) AS learners
  FROM cohorts
  GROUP BY cohort_week
),
activity AS (
  SELECT DISTINCT
    learner_id,
    date_trunc('week', event_time)::date AS active_week
  FROM learning.events
  WHERE event_name = 'lesson_completed'
),
retained AS (
  SELECT
    c.cohort_week,
    (a.active_week - c.cohort_week) / 7 AS week_index,
    COUNT(DISTINCT a.learner_id) AS active_learners
  FROM cohorts c
  JOIN activity a
    ON a.learner_id = c.learner_id
   AND a.active_week >= c.cohort_week
  GROUP BY c.cohort_week, week_index
)
SELECT
  r.cohort_week,
  r.week_index,
  r.active_learners,
  s.learners AS cohort_learners,
  ROUND(100.0 * r.active_learners / NULLIF(s.learners, 0), 1) AS retention_pct
FROM retained r
JOIN cohort_size s USING (cohort_week)
ORDER BY r.cohort_week, r.week_index;

date - date in PostgreSQL yields an integer number of days, so dividing by 7 yields a week index. Casting date_trunc to date before the subtraction is the step that makes the arithmetic integer rather than interval. Week 0 is the enrollment week. A learner who completes a lesson that week is retained at index 0, which is activity, not a prediction. Publish the definition next to the chart.

A window still earns its place when the export is learner-level: MIN(enrolled_at) OVER (PARTITION BY learner_id) assigns the cohort on every activity row so a scientist can filter without rejoining. That export is a different product from the retention table. Name both.

Session keys

sqlCopy
WITH ordered AS (
  SELECT
    learner_id,
    event_id,
    event_name,
    event_time,
    LAG(event_time) OVER (
      PARTITION BY learner_id
      ORDER BY event_time, event_id
    ) AS prev_event_time
  FROM learning.events
),
flagged AS (
  SELECT
    learner_id,
    event_id,
    event_name,
    event_time,
    CASE
      WHEN prev_event_time IS NULL THEN 1
      WHEN event_time - prev_event_time > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sequenced AS (
  SELECT
    learner_id,
    event_id,
    event_name,
    event_time,
    SUM(is_new_session) OVER (
      PARTITION BY learner_id
      ORDER BY event_time, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM flagged
)
SELECT
  learner_id,
  session_seq,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*) AS event_count,
  COUNT(*) FILTER (WHERE event_name = 'lesson_completed') AS lessons_completed
FROM sequenced
GROUP BY learner_id, session_seq;

The running sum uses ROWS, and the order includes event_id. If two events share event_time and the frame is RANGE ordered only by event_time, both rows are peers, both see both flags, and both receive the same session_seq even when one of them should have opened a new session. That bug is invisible on factory data where every timestamp is unique and loud on production mobile clients that batch events with a truncated clock.

Progress inside a course

The in-product "you have finished 6 lessons" strip is a window on completion grain:

sqlCopy
SELECT
  learner_id,
  course_id,
  lesson_slug,
  completed_at,
  ROW_NUMBER() OVER (
    PARTITION BY learner_id, course_id
    ORDER BY completed_at, lesson_slug
  ) AS completion_seq,
  COUNT(*) OVER (
    PARTITION BY learner_id, course_id
  ) AS lessons_completed,
  completed_at - MIN(completed_at) OVER (
    PARTITION BY learner_id, course_id
  ) AS since_first_completion
FROM learning.events
WHERE event_name = 'lesson_completed';

COUNT(*) OVER (PARTITION BY learner_id, course_id) has no ORDER BY, so it is the partition total, not a running count. ROW_NUMBER is the running position. Using COUNT(*) with an ORDER BY and the default frame would turn the total into a running count and the strip would say "1 of 1", "2 of 2", "3 of 3" as the learner advances through history. If the product wants "3 of 42 planned lessons," the denominator is the course outline's lesson count, joined from the catalog, not COUNT(*) over completions. The window counts what happened. The catalog counts what was assigned.

Named windows and filters

When several functions share a frame, name it. The name is documentation and a single place to edit the tie-break.

sqlCopy
SELECT
  learner_id,
  paid_at,
  amount_cents,
  SUM(amount_cents) OVER learner_pay AS running_cents,
  AVG(amount_cents) OVER learner_pay AS running_avg_cents
FROM billing.payment_attempts
WHERE status = 'succeeded'
WINDOW learner_pay AS (
  PARTITION BY learner_id
  ORDER BY paid_at, attempt_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

FILTER applies to the aggregate before the frame consumes it. COUNT(*) FILTER (WHERE event_name = 'lesson_completed') OVER (PARTITION BY learner_id) counts completions in the partition while the query still returns every event. That is how a session export can show "events in this session" and "completions in this session" without splitting the fact table. DISTINCT inside a window aggregate is not supported in PostgreSQL. If you need a distinct count per partition on every row, compute it in a grouped CTE and join it back. The join is easier to explain than a dialect-specific workaround.

Nulls, time zones, and late days

LAG of a nullable measure propagates nulls into the gap arithmetic. A null event_time should never enter the table. A check constraint or an upstream test belongs with the pipeline, because ORDER BY event_time puts those rows at one end of the partition and opens phantom sessions.

Store timestamptz. Truncate to a reporting zone in the query that defines the day: date_trunc('day', event_time AT TIME ZONE 'Asia/Kolkata') produces a timestamp without zone in that local calendar. Do the truncation once, in one CTE, and window over the result. Truncating in the SELECT list and ordering by the original timestamp means the "day" on the chart and the "day" inside the frame disagree for events near midnight.

A late event with an old event_time changes LAG, session sequences, first-touch timestamps, and every running sum that includes it. The query is deterministic given the table. The table is not stable until the ingestion watermark is. The operational rule for TechLearningPro's daily dashboard is: recompute the reporting day and the previous day, because mobile clients upload yesterday's events this morning. A window query that is "correct once" and then incrementally patched with only today's inserts will miss that rewrite. Full recompute of the affected partitions is the honest batch design. Module 2 turns that sentence into a scheduled task with a bounded lookback.

Practical Implementation or Design Walkthrough

Problem

Build the October progress pack for Data Engineering Foundations: an ordered funnel per course, a weekly retention table for new enrollments, and a session summary a researcher can join back to events. The pack runs in PostgreSQL. It must survive duplicate event loads. It must define what "reached the next step" means. It must be cheap enough to recompute every morning for a table of tens of millions of events, with a date predicate that keeps each run to the lookback window plus the cohort history the chart still shows.

Source and target

Source tables are learning.events, learning.enrollments, and billing.payment_attempts as defined above. Expected volume at the current company stage is about 8 million learning events per month, a few hundred thousand enrollments, and payment attempts well below event volume. Freshness is next-morning, not minute-level. Quality rules: event_id is unique in the published set; event_time is not null; funnel steps are ordered; cohort size is a count of enrollments, not a count of grouped activity rows; session gap is 30 minutes.

Targets:

session_seq is only meaningful together with as_of_date, because a late event can renumber sessions. Consumers that need a stable id should use (learner_id, session_start) after the recompute settles, and they should tolerate a start time that moves if an earlier event arrives.

Implementation steps

1. Land events with a primary key on event_id. The window dedupe is defense in depth for logical duplicates, not a substitute for the constraint.
2. Build the deduped CTE restricted to the reporting interval, plus one day of lookback on ingested_at so a row that describes yesterday's learning and arrived today is visible. Document the predicate. A predicate on ingested_at alone hides the learner's history. A predicate on event_time alone hides late arrivals until someone notices yesterday changed. The funnel uses event_time for the business window and includes late arrivals by recomputing the last two event days every run.
3. Compute first-touch timestamps with MIN(...) FILTER at (learner_id, course_id).
4. Apply the order rule in its own CTE so a unit test can select reached_payment without re-deriving it.
5. Aggregate to the course grain for the chart.
6. Separately, assign cohort_week, compute cohort_size, aggregate activity, and divide.
7. Separately, sessionize the lookback window. Do not sessionize inside the funnel CTE. The grains differ.
8. INSERT into the target tables inside a transaction that replaces the month or the as-of date you recomputed. A pattern that works on PostgreSQL is to load a staging table, then DELETE the affected keys and INSERT the staging rows in one transaction. The dashboard never reads a half-replaced month. Idempotency comes from the replacement of a key range, not from hoping the window ignores duplicates.
9. Grant the dashboard role SELECT on the analytics schema only. The role that runs the SQL is not the role the BI tool uses.

Validation

• A fixture with two course_view rows for one learner counts one viewer.
• A fixture with lesson_completed before payment_succeeded counts a completion only in the unordered diagnostic, never in reached_completion.
• A fixture with two events 31 minutes apart yields two sessions. A fixture with two events 29 minutes apart yields one.
• A fixture with two events at the same event_time and different event_id yields a stable session_seq across two runs.
• SUM(viewers) across courses can exceed distinct learners, because the grain is course. The company-level learner count is a different query without course_id in the partition. Publish both or publish one. Do not let a slide add them.
• Compare payers to COUNT(DISTINCT order_id) of succeeded payments for the same window. They will disagree when a payment has no preceding checkout_started. That disagreement is a data-quality signal, not a rounding error. Write it to a reconciliation row.

Why this is the transformation Module 2 will run

The SQL is already an ELT transform: extract is the application writing events, load is the insert into learning.events, transform is this query. Data Integration Patterns names the pattern. ETL and ELT Architecture decides where the SQL runs. Nothing in the window logic requires the transform to move off PostgreSQL while the data fits and the freshness is overnight. When event volume makes the morning sort compete with product traffic, the same CTEs move to a warehouse table loaded from a replica, which is an ELT placement decision. The funnel contract does not change when the engine does. The QUALIFY shortcut might become available. The CTE remains the reviewable form until that day.

Enterprise Real-World Scenarios

TechLearningPro progress funnel that finance and product can both sign

Product wanted "percent of viewers who become completers." Finance wanted "percent of paid learners who complete a lesson," because a completer who never paid does not fund the course. The first draft used one GROUP BY learner_id with independent MAX flags. Product's percentage looked healthy. Finance's cohort, joined later, showed completions without payments. The repair was the ordered-funnel CTE in this lesson, plus a second output that starts at payment_succeeded and ignores the view step. Same events, two denominators, both labeled. The dashboard stopped using a single "conversion" word.

Volume was the October window: 1.2 million deduped events, 180,000 viewers, low hundreds of thousands of attempt rows. The morning job restricted event_time to the month plus a two-day late-arrival margin and finished in minutes on the analytics replica, not on the primary that takes enrollments. The replica lag was part of the contract: the job reads pg_last_xact_replay_timestamp() and refuses to publish if the replica is more than five minutes behind. That check is a SQL function on the replica, not a new service. Lesson 8 explains what the replica is replaying.

Security sat in the grant list. The events table includes nothing that is a payment credential, but it does include the learner id. The published funnel is aggregated to course_id. The learner-level export exists for research and is granted to a role that is not embedded in the company-wide BI folder. A window query is not a reason to widen access to raw events "so people can slice it themselves."

TechLearningPro session quality, and a tie that moved a metric

A researcher defined a session as LAG over event_time with a 30-minute gap and a default RANGE running sum. In staging, every timestamp was unique and the session count matched a hand count. In production, the mobile client floored timestamps to the second and uploaded two events in one batch. Peers shared a timestamp. The RANGE frame assigned one session sequence to two events that the flag logic had intended to split when one of them carried a stale clock. The visible symptom was a handful of sessions with a negative gap after a later cleanup job reordered by event_id. The repair was the ROWS frame and the composite order (event_time, event_id) shown above, plus a producer fix to send timestamptz from the device clock and the server receipt time as two columns. The window orders by server receipt when the research question is "what did the pipeline see," and by device time when the question is "what did the learner experience." Those are different ORDER BY clauses. They need different column names on the chart.

A marketplace pattern worth copying, described only as a pattern

Large commerce and content companies publish the same shape under different product names: an ordered funnel from impression to purchase, a session derived from an inactivity gap, and a dedupe window on an event id. The engineering content that transfers is the grain contract and the frame contract. The threshold (30 minutes, a calendar day, a strict sequence of page types) is a product decision. Copying a threshold from a public talk without writing the fixture that proves it is how TechLearningPro once counted a learner who left for lunch as two people. The fixture is part of the metric.

Best Practices

• Declare the grain of every CTE in a one-line comment: event, attempt, learner-course, course-month, cohort-week. Reviewers should be able to see the collapse points without reconstructing them.
• Put a unique tie-break in every analytical ORDER BY that feeds ROW_NUMBER, LAG, or a ROWS frame. event_id or attempt_id is enough when it is unique.
• Write the frame. The default is a real behavior, and it is the wrong behavior often enough that an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is cheaper than an incident.
• Dedupe before you rank, funnel, or sessionize. A duplicate event becomes a false step, a false session boundary, and a double-counted running total.
• Filter time in one CTE, in one zone, and select from that CTE. Repeating event_time >= ... with a slightly different bound in two branches splits the population.
• Prefer GROUP BY when the consumer does not need the detail rows. A window that is immediately aggregated threw away the only advantage a window has, after paying for a sort.
• Keep funnel step names and order in a small reference table (step_name, step_order) if more than one query implements the funnel. Joining to that table beats a copied CASE.
• Test ties, nulls, replays, and out-of-order steps as fixtures. The happy path with strictly increasing unique timestamps hides every bug this lesson is about.
• Recompute affected dates when late data is allowed. Do not publish a running sum as an incremental number unless the pipeline rejects late events.
• Grant consumers the aggregated outputs. Keep learner-level window exports on a role that matches the sensitivity of a learner timeline.
• When the dialect gains QUALIFY, treat the adoption as a readability change that must produce the same rows as the CTE. Pin that comparison in a test.
• Match the index to the partition and order you actually run every morning, and confirm with EXPLAIN that the sort disappeared for that query. Lesson 8 is the method. An unused index still costs the ingestion path.

Common Mistakes and Anti-Patterns

Filtering the window in WHERE. The query fails to parse, or someone repeats the window expression in WHERE, which still fails because window functions are not allowed there. The workaround of copying the call into a subquery is the design, not a workaround. Consequence of "fixing" it by pre-aggregating too early: you drop the row you needed for LAG. Compute the window on the right grain, then filter outside.

LAST_VALUE with the default frame. The chart of "final lesson in the course, shown on every row" repeats the current lesson instead. Product files a bug against the event collector. The collector is fine. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or compute the value once with a grouped MAX and join it.

ROWS used as if it were time. A seven-row moving average on raw events tracks launch-minute noise and then looks "flat" on quiet days because seven quiet events stretch across a week. Build a daily grain or use RANGE with an interval. State whether missing days are zeros or absences.

Unstable ROW_NUMBER on ties. Two pipeline runs pick different "latest" attempts when ORDER BY attempted_at ties and there is no attempt_id. Downstream dedupe deletes a different row each night. Payments reconciliation flaps. Extend the order with a unique key, and make the sort direction match the business meaning of "latest."

Flag funnels that ignore order. Completions without payments inflate the top of the funnel or crash the ratio. Support enrollments and replayed historical imports are enough to do it. Encode order as timestamp comparisons and publish the excluded population as its own count so nobody reintroduces it to "make the numbers match."

Session sequences treated as durable keys. A streaming consumer stores learner_id || session_seq and later joins to a recomputed batch. After one late event, every session id for that learner shifts and the join duplicates or drops history. Persist session_start after the data is closed, or recompute both sides from events.

Window over a join that multiplied rows. Joining events to a course table that has two current price rows doubles events. LAG then returns the sibling price row's event time, and the gap is zero. The session count collapses. Dedupe dimensions before the window, or join dimensions after the window has produced the keys you need.

Distinct counts inside a window by wish. PostgreSQL rejects COUNT(DISTINCT learner_id) OVER (...). A nested subquery that "approximates" it with ROW_NUMBER = 1 and then counts is valid if the partition matches the distinct key. A copy-paste that partitions by the wrong column silently counts the wrong set. Prefer a grouped CTE when the chart is an aggregate. The plan is simpler and the grain is obvious.

Sorting the presentation and assuming the window follows. ORDER BY event_time at the end of the query does not change OVER (PARTITION BY learner_id ORDER BY ingested_at). The previous-event column will not match the visual order. Set the window order to the sequence the metric claims to use, and let the presentation order be whatever the page needs.

Troubleshooting and Design Review

A design review of a new metric asks six questions before it asks about style.

1. What is the grain of the published table, and which CTE first reaches that grain?
2. What is the partition, the order, and the frame of every OVER?
3. What happens on a tie, a null, a replay, and an out-of-order step? Is there a fixture?
4. Is a window doing work a GROUP BY could do more cheaply?
5. Which dates must be recomputed when data arrives late, and who is allowed to read learner-level output?
6. Does the SQL still mean the same thing if it moves from PostgreSQL to the warehouse dialect Module 2 chooses?

If the author cannot answer 2 and 3, the query is not ready to schedule. EXPLAIN comes next, on production-shaped volume, which is lesson 8's craft. A review that only checks formatting will approve a default frame.

Interview Questions and Answers

Q1. Why does a window function return a different number of rows from GROUP BY when both can compute SUM?

GROUP BY defines the output grain. One group becomes one row, and the inputs are not returned unless you aggregate them into arrays or join them back. A window function defines a peer set and a calculation, then attaches the result to each input row. SUM(amount) OVER (PARTITION BY learner_id) repeats the learner total on every payment. SUM(amount) GROUP BY learner_id returns one row per learner. Choose the window when the consumer still needs the payment. Choose the group when the consumer wants the total. Doing both is normal: window in an inner CTE, group in the outer query.

Q2. What is the default frame in PostgreSQL when OVER includes ORDER BY, and why does that surprise people using LAST_VALUE?

The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In a RANGE frame, the current row includes peers that compare equal on the ORDER BY expressions. LAST_VALUE looks at the end of that frame, which is the current peer, so on each row it returns that row's value instead of the last value in the partition. The frame that matches "last in the partition" runs through UNBOUNDED FOLLOWING. Ranking functions and LAG / LEAD do not use this frame; they use the order and an offset. Aggregates used as windows do use it.

Q3. When do you choose ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER assigns a unique position and is the right tool for "keep one row per partition" once you have defined a total order. Ties are broken only if ORDER BY is unique. RANK gives tied rows the same number and skips the next numbers, which matches a competition leaderboard. DENSE_RANK gives tied rows the same number and continues with the next integer, which matches "how many distinct values are ahead of this row." NTILE is a different idea: it buckets by position into a requested count of tiles, and small partitions do not produce meaningful quantiles.

Q4. Why is QUALIFY ROW_NUMBER() ... = 1 not the query you run on TechLearningPro's PostgreSQL database, and what do you run instead?

QUALIFY filters after window evaluation. Snowflake, BigQuery, and Databricks SQL support it. PostgreSQL does not, and it is not part of the SQL you can assume in every Spark job. The portable form computes the window inside a CTE or subquery and filters in the outer query: WHERE attempt_rank = 1. That form is also easier to test, because you can select the ranked rows without the filter. If a future warehouse model uses QUALIFY, it should be checked against this CTE rather than treated as a new business rule. PostgreSQL's DISTINCT ON is a local alternative for "first row per key," provided the ORDER BY starts with those keys and then states the tie-break.

Q5. A dedupe query uses ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY attempted_at DESC). Two attempts share attempted_at. What can go wrong in production?

The two runs can keep different rows, because ROW_NUMBER must pick a unique order and the declared order is a tie. One night the captured attempt survives. The next night the declined retry survives. Reconciliation against the payment provider flaps, and a downstream delete-insert job changes history it meant to freeze. Add a unique, immutable tie-break such as attempt_id DESC, and decide which status wins if business rules care about more than time. A primary key on the attempt does not save you: the partition is order_id, so many attempts are supposed to exist. The window is choosing among them.

Q6. Explain a 30-minute sessionization with window functions. Why is a late event expensive?

LAG(event_time) within PARTITION BY learner_id ORDER BY event_time, event_id exposes the previous event. A new session starts when there is no previous event or the gap exceeds 30 minutes. A running SUM of that flag, with a ROWS frame and the same order, assigns session_seq. A late event with an earlier timestamp changes the lag of the following event and can shift every later sequence for that learner. The query is a full recompute of the affected learner history, not an incremental counter. Treat session_seq as derived for a stated as-of time. Stable session identity, if you need it, has to be defined on closed data or stored when the session is sealed.

Q7. How do you stop an ordered product funnel from reporting more completers than payers?

Record the first timestamp of each step per learner and course. Count a learner at a step only when that timestamp is present and at or after the previous step's timestamp, all the way back to the entry step. Independent boolean flags answer "did this event ever happen," which counts comped enrollments, imports, and replays as if they had walked the funnel. Keep the excluded learners as a published residual count so the gap between "ever completed" and "completed after paying" is visible. Dedupe events before you take MIN(event_time).

Q8. Where can an index remove the sort of a window, and where can it not?

An index that provides rows in PARTITION BY order and then ORDER BY order can let the planner walk the index instead of sorting. For PARTITION BY learner_id ORDER BY event_time, event_id, the index (learner_id, event_time, event_id) matches. An index on event_time alone does not, because the partition is the learner. An index does not remove the need to read the rows the frame touches, and a RANGE frame on an expression such as date_trunc('day', event_time) will not match an index on the raw timestamp unless you index the same expression. Confirm with EXPLAIN. A window that then joins to a multiplied dimension can still explode after a perfect index scan.

Q9. Why does COUNT(*) OVER (PARTITION BY learner_id ORDER BY completed_at) disagree with "lessons completed in the course"?

With ORDER BY and no explicit frame, the aggregate uses the default frame ending at the current peer. The count becomes a running count, so the last lesson shows the total and earlier lessons show a prefix. "Lessons completed in the course, shown on every row" is COUNT(*) OVER (PARTITION BY learner_id, course_id) with no ORDER BY, or an explicit frame through UNBOUNDED FOLLOWING. "Position of this lesson" is ROW_NUMBER. "Lessons that exist in the outline" is a join to the catalog. Those three numbers are different metrics and the product strip usually needs the position and the catalog denominator.

Q10. How do you compute a trailing seven-day average so that a day with five payments does not count as five days?

Collapse to one row per day first, or use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW on a date or timestamp order key. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts rows. On raw payments, a launch morning can fill the whole frame. Also decide what a missing day means. Absent rows do not contribute a zero unless you generate the calendar and left join revenue onto it. Bound that calendar so the months before a course existed do not become a long zero average.

Q11. You need the rank of each learner by lifetime revenue, and also their latest payment time. Why is nesting both windows in one expression invalid, and how do you structure it?

PostgreSQL does not allow a window function to wrap another window function in the same SELECT. The lifetime revenue is one window or, better, one grouped aggregate. The rank is a window over that result. Use a CTE for revenue per learner, and an outer query with RANK() OVER (ORDER BY revenue_cents DESC, learner_id). The latest payment time belongs in the same grouped CTE as MAX(paid_at) if you do not need payment detail. Layering makes the grain change visible and gives the test suite a place to assert revenue before rank exists.

Q12. A stakeholder asks to add QUALIFY now so the warehouse migration is easier. What do you agree to, and what do you refuse?

You agree that the dedupe rule should be written once and should survive a move to a dialect that has QUALIFY. You refuse to make the PostgreSQL job depend on syntax it cannot parse, and you refuse a second definition of "latest attempt" that only the warehouse query uses. The CTE filter is the contract. A later pull request may project that CTE into QUALIFY in a warehouse model file, with a row-comparison test against the CTE. Ease of migration comes from one grain, one order, and one tie-break, not from using a clause the current production engine rejects.

Hands-On Exercises

Exercise 1. Ordered funnel fixture

Problem. Implement the October ordered funnel for one course on a local PostgreSQL. Include a learner who completes a lesson without paying, a learner who pays and completes, a replayed payment_succeeded with a new event_id and the same business key you define, and two events that share event_time.

Tasks.

1. Create the tables from this lesson and load the fixture.
2. Write the dedupe, first-touch, and ordered-step CTEs.
3. Assert the unpaid completer is absent from reached_completion and present in a residual count.
4. Assert the replay does not create a second payer.
5. Document the tie-break column and the timestamp comparison (>= or >).
6. Show EXPLAIN for the dedupe query and state whether a sort remains.

Deliverables. SQL file, fixture, expected counts, and a short note on what would have to be recomputed if a late September event arrived on 2 November.

Rubric.

Exercise 2. Sessions and a seven-day average

Problem. Sessionize a two-day event fixture with a 30-minute gap, and compute a trailing seven-day average on a daily revenue fixture that contains a gap day and a day with three payments.

Tasks.

1. Produce session_seq with LAG and a ROWS running sum.
2. Show the wrong session_seq you get if the frame is RANGE and timestamps tie. One paragraph is enough.
3. Build course_revenue_by_day, insert a missing day as a calendar gap, and write both a ROWS average and a RANGE INTERVAL average.
4. State whether the gap day counts as zero. Make the SQL match the statement.
5. Note which of these queries you would schedule in Module 2 as a daily replace of a date partition, and which output column is unsafe as a permanent identifier.

Acceptance. The 31-minute gap opens a session. The 29-minute gap does not. The three payments on one day contribute one day to the trailing window in the RANGE query. The SQL uses a CTE a second job could reuse.

Lesson Summary

A window function annotates rows. PARTITION BY chooses the peers, ORDER BY sequences them, and the frame decides which peers an aggregate can see. The default frame is RANGE up through the current peer, which makes LAST_VALUE and tied running totals surprise people who pictured a physical row boundary. ROWS counts rows. RANGE with an interval counts time. ROW_NUMBER dedupes only when the order is total. LAG plus a running sum of gap flags derives sessions that a late event can renumber.

Funnels become trustworthy when each step has a first timestamp and the next step must follow it. Cohort charts become trustworthy when cohort size is computed from enrollments, not from the activity groups you happen to have in the same SELECT. QUALIFY is a readable filter in several warehouses and a syntax error in PostgreSQL. The CTE that filters a window result is the contract TechLearningPro can run now and can carry into Data Integration Patterns and ETL and ELT Architecture without rewriting the metric.

The next lesson, Relational Database Internals, explains what PostgreSQL is doing while these queries run: buffers, WAL, MVCC snapshots, indexes, vacuum, and the EXPLAIN (ANALYZE, BUFFERS) output you need when the morning window sort starts competing with the product. The SQL in this lesson is the workload that lesson will tune. The lesson after the internals pair, on modeling, is where the event and enrollment tables stop being examples and become a schema you can defend.

Further Reading

• PostgreSQL, Window Function Documentation and the window function calls section of the SQL syntax chapter. These are the source for default frames, GROUPS, and EXCLUDE.
• PostgreSQL, DISTINCT ON in the SELECT reference, for the PostgreSQL-specific dedupe form.
• TechLearningPro companions: Window Functions, Ranking Functions, LEAD & LAG, Common Table Expressions, and Window Functions.
• Snowflake QUALIFY, Google BigQuery QUALIFY, and Databricks QUALIFY for the dialect this lesson deliberately replaced with a subquery.
• PostgreSQL EXPLAIN, used in the exercises and taught as internals in the next lesson.