SQL Window Functions — Applied LEAD, FIRST

QUESTION 6

LEAD() — Look Ahead to Calculate Growth and Differences

LEADORDER BYGrowth AnalysisThe Opposite Direction from LAG

Background ▼

LEAD(expr, offset, default) returns the value offset rows ahead of the current row. The default offset is one row. When no next row exists, such as on the last row of a partition, it returns default, or NULL when the default is omitted.

LEAD(revenue)         -- revenue from 1 row ahead (default)
LEAD(revenue, 2)      -- revenue from 2 rows ahead
LEAD(revenue, 1, 0)  -- 1 row ahead; return 0 when that row does not exist

How it differs from LAG: While LAG() refers to the past—the preceding row—LEAD() refers to the future—the following row. For both, ORDER BY decides which row counts as next or previous. They are commonly used for month-over-month or week-over-week comparisons and growth-rate calculations.

Problem ▼

From the monthly_revenue table below, return each month’s revenue, the following month’s revenue as next_revenue, and the difference from the following month as diff = next_revenue − revenue. Return NULL when no following month exists.

Input Table

▸ monthly_revenue

month	revenue
2026-01	100
2026-02	120
2026-03	90
2026-04	150

Expected Output ▼

month	revenue	next_revenue	diff
2026-01	100	120	+20
2026-02	120	90	-30
2026-03	90	150	+60
2026-04	150	NULL	NULL

Model Answer ▼

SELECT
  month,
  revenue,
  LEAD(revenue) OVER(ORDER BY month) AS next_revenue,   -- Look 1 row ahead for revenue; use LEAD(revenue, 2) for 2 rows ahead
  LEAD(revenue) OVER(ORDER BY month) - revenue AS diff  -- Difference from next month; arithmetic involving NULL returns NULL

FROM monthly_revenue
ORDER BY month;

/*
  Execution order:
  1. FROM monthly_revenue (load all 4 rows)
  2. OVER(ORDER BY month) sorts by month ascending and establishes row order
  3. LEAD(revenue) retrieves revenue from 1 row after the current row
  4. Calculate diff = LEAD(revenue) - revenue
  5. ORDER BY month returns the rows in ascending month order
  */

Explanation (table transitions & key points) ▼

SELECT
  month, revenue,
  LEAD(revenue) OVER(ORDER BY month) AS next_revenue,
  LEAD(revenue) OVER(ORDER BY month) - revenue AS diff
FROM monthly_revenue
ORDER BY month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM monthly_revenueLoad the table containing four months of revenue data. Row order is not yet established, so LEAD() has not been evaluated.

1 / 4

month	revenue
2026-01	100
2026-02	120
2026-03	90
2026-04	150

All 4 rows loaded

LEARNING POINTS

Choosing between LAG and LEAD:Use LAG() when you need the preceding row, such as comparing with last month’s actual result, and LEAD() when you need the following row, such as comparing with next month’s forecast. LAG and LEAD can be made equivalent by reversing the ORDER BY direction: LAG(x) OVER(ORDER BY date DESC) produces the same result as LEAD(x) OVER(ORDER BY date ASC).

A default value can avoid NULL:If a NULL on the last row would disrupt a later calculation, specify a default value, as in LEAD(revenue, 1, 0). However, that value may be interpreted as zero-dollar growth, so decide during design whether the business context calls for COALESCE, a different default, or preserving NULL.

⊞ LAG vs LEAD — Opposite Reference Directions over the Same Data

month	revenue	LAG(revenue) Previous month’s revenue	LEAD(revenue) Next month’s revenue
2026-01	100	NULL (no previous month)	120
2026-02	120	100	90
2026-03	90	120	150
2026-04	150	90	NULL (no next month)

Returning LAG and LEAD together makes it possible to compare both neighboring values on a single row. This is also useful for anomaly detection in time-series data, such as finding rows that differ sharply from both neighbors.

ANTI-PATTERNS

LEAD() without ORDER BY is nondeterministic:If ORDER BY is omitted as in LEAD(revenue) OVER(), the database engine can process rows in an arbitrary order, so which row counts as next may vary between executions. Always establish the sequence explicitly, as in OVER(ORDER BY date).

Omitting PARTITION BY mixes data from different users:When each user or department needs its own next row, always specify PARTITION BY, as in LEAD(revenue) OVER(PARTITION BY user_id ORDER BY month). Otherwise, the function can cross from one user’s rows into another’s.

QUESTION 7

FIRST_VALUE() — Calculate the Gap from the Category Leader

FIRST_VALUEPARTITION BYCategory ComparisonThe LAST_VALUE Trap

Background ▼

FIRST_VALUE(expr) returns the value from the first row of the window frame. Combined with PARTITION BY category ORDER BY score DESC, it consistently retrieves the highest score—the leader—in each category.

FIRST_VALUE(score) OVER(
  PARTITION BY category
  ORDER BY score DESC
) AS top_score  -- Highest score in the category

The default frame and tied rows: When ORDER BY is present, the default frame—normally RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—always includes the first row, so every current row receives the value from that first row. If values tie, rows sharing the same ordering value are peers in the same frame, but the value FIRST_VALUE returns does not change.

Problem ▼

From the product_scores table below, return each product’s score, the highest score in the same category as top_score, and the gap from the leader as gap = top_score − score. Sort products by descending score within each category.

Input Table

▸ product_scores

product	category	score
P1	A	85
P2	A	92
P3	A	78
P4	B	70
P5	B	88

Expected Output ▼

product	category	score	top_score	gap
P2	A	92	92	0
P1	A	85	92	7
P3	A	78	92	14
P5	B	88	88	0
P4	B	70	88	18

Model Answer ▼

SELECT
  product,
  category,
  score,
  FIRST_VALUE(score) OVER(  -- First row after descending category sort = highest category score
    PARTITION BY category
    ORDER BY score DESC
  ) AS top_score,
  FIRST_VALUE(score) OVER(  -- Gap from the leader (top_score - this product's score)
    PARTITION BY category
    ORDER BY score DESC
  ) - score AS gap

FROM product_scores
ORDER BY category, score DESC;

/*
  Execution order:
  1. FROM product_scores (all 5 rows)
  2. PARTITION BY category divides the rows into category A (3 rows) and B (2 rows)
  3. ORDER BY score DESC sorts each category by descending score
  4. FIRST_VALUE(score) attaches the first row's score to every row in its partition
  5. Calculate gap = FIRST_VALUE(score) - score
  6. ORDER BY category, score DESC produces the final output
  */

Explanation (table transitions & key points) ▼

SELECT
  product, category, score,
  FIRST_VALUE(score) OVER(
    PARTITION BY category
    ORDER BY score DESC
  ) AS top_score,
  FIRST_VALUE(score) OVER(
    PARTITION BY category ORDER BY score DESC
  ) - score AS gap
FROM product_scores
ORDER BY category, score DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM product_scoresLoad five product rows. Categories A with three products and B with two are mixed together. Neither their groups nor their order is established yet.

1 / 4

product	category	score
P1	A	85
P2	A	92
P3	A	78
P4	B	70
P5	B	88

All 5 rows loaded

LEARNING POINTS

FIRST_VALUE vs MAX():MAX(score) OVER(PARTITION BY category) can also retrieve the highest category score, but FIRST_VALUE is especially valuable because it can retrieve nonnumeric values. For example, FIRST_VALUE(product) OVER(PARTITION BY category ORDER BY score DESC) returns the name of the top-scoring product, while MAX(product) does not answer that question.

The LAST_VALUE() trap:LAST_VALUE(score) OVER(PARTITION BY category ORDER BY score DESC) does not return the value from the last row of the entire partition as intuition might suggest. Because the default frame runs from the first row through the current row, including tied peers, each row gets the value at the end of its current frame; only the final row’s frame reaches the minimum. To return the true final value on every row, explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, as shown below.

Why LAST_VALUE() Defies Intuition: The Frame Trap

✗ LAST_VALUE with the default frame (Category A)

product	score	LAST_VALUE (default = first through current row)
P2	92	92 ← current row itself
P1	85	85 ← current row itself
P3	78	78 ← current row itself (= minimum)

Because the frame runs from the first row through the current row, the partition’s final value is unavailable until the final row

✓ LAST_VALUE with an explicit frame (Category A)

product	score	LAST_VALUE (through UNBOUNDED FOLLOWING)
P2	92	78 ← always the minimum
P1	85	78 ← always the minimum
P3	78	78 ← always the minimum

It behaves as expected only after adding ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

For that reason, MIN(score) OVER(PARTITION BY category) is safer and simpler than LAST_VALUE for retrieving the category minimum. Treat LAST_VALUE as an advanced function whose frame usually needs to be explicit.

QUESTION 8

Moving Average — Implement a Three-Day Sliding Window

ROWS BETWEENAVG + PRECEDINGMoving AverageSliding Window

Background ▼

A moving average calculates the average over the latest N periods and is widely used to track trends in stock prices, sales, traffic, and other time-series data. With window functions, define the frame using ROWS BETWEEN.

AVG(amount) OVER(
  ORDER BY sale_date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)  -- Average over the latest 3 days: 2 preceding rows through the current row

Moving average vs. cumulative average: Because the frame slides along the ordered rows, it always references only the latest N records as data grows. That range differs fundamentally from a cumulative average, which averages every row from the beginning through the current row. On the earliest rows, where the frame cannot reach far enough back, only the rows that exist are included.

Problem ▼

From the daily_sales table below, calculate each day’s sales and the three-day moving average through that day as moving_avg_3. This table contains exactly one row per day with no missing dates. Return rows by ascending date; for the initial rows with fewer than three days of data, average only the rows that exist.

Input Table

▸ daily_sales

sale_date	amount
04-01	30
04-02	50
04-03	40
04-04	60
04-05	80

Expected Output ▼

sale_date	amount	moving_avg_3
04-01	30	30.00
04-02	50	40.00
04-03	40	40.00
04-04	60	50.00
04-05	80	60.00

Model Answer ▼

SELECT
  sale_date,
  amount,
  AVG(amount) OVER(                           -- Average from 2 rows before through current row: up to 3 rows
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3

FROM daily_sales
ORDER BY sale_date;

/*
  Frame movement (sliding):
  04-01: frame = [04-01]             → AVG(30) = 30.00
  04-02: frame = [04-01, 04-02]      → AVG(30, 50) = 40.00
  04-03: frame = [04-01 through 04-03] → AVG(30, 50, 40) = 40.00
  04-04: frame = [04-02 through 04-04] → AVG(50, 40, 60) = 50.00  ← 04-01 leaves
  04-05: frame = [04-03 through 04-05] → AVG(40, 60, 80) = 60.00  ← 04-02 leaves

  ★ "2 PRECEDING" means include up to 2 rows before the current row.
    The frame therefore contains at most 3 rows: 2 preceding rows plus the current row.
    Near the beginning, the calculation automatically uses only the rows that exist.

  ★ N-day moving average = ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW
*/

Explanation (table transitions & key points) ▼

SELECT
  sale_date, amount,
  AVG(amount) OVER(
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_sales
ORDER BY sale_date;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesLoad five days of sales data. We will define a frame that dynamically references the latest three days and use it to calculate a moving average.

1 / 4

sale_date	amount
04-01	30
04-02	50
04-03	40
04-04	60
04-05	80

All 5 rows loaded

LEARNING POINTS

Formula for an N-day moving average:ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW. Use 6 PRECEDING for a seven-day moving average or 29 PRECEDING for a 30-day average. This is a standard dashboard pattern for metrics such as the average DAU, or daily active users, over the latest seven days.

ROWS counts rows, not calendar days:This formula represents an N-day moving average only when the data has one row per day with no missing dates, as it does here. If a date has multiple rows or dates are missing, aggregate and fill the daily series first, or use a time-range frame supported by your database dialect.

Moving average vs cumulative average:A moving average using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW reads only the latest three rows, excluding older data so it can track current trends. A cumulative average using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW averages the full history, showing a long-term trend but responding more slowly to recent changes.

⊞ Cumulative Average vs Moving Average over the Same Data

sale_date	amount	Cumulative average (UNBOUNDED through CURRENT)	Three-day moving average (2 PRECEDING through CURRENT)
04-01	30	30.00	30.00
04-02	50	40.00	40.00
04-03	40	40.00	40.00
04-04	60	45.00 ← dampened by the full history	50.00 ← reflects only the latest 3 days
04-05	80	52.00 ← earlier values still pull it down	60.00 ← responds quickly to the rising trend

When a sharp increase occurs as on 04-05, the moving average of 60.00 detects it faster than the cumulative average of 52.00. Moving averages are effective for trend tracking and anomaly detection.

ANTI-PATTERNS

Using RANGE, the default, can produce the wrong moving average when a date has multiple rows:If the frame is omitted, it implicitly becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Multiple rows on the same date form a peer group, and RANGE includes that entire group, producing a cumulative average rather than the intended moving average. Always specify ROWS BETWEEN for this row-based moving average.

QUESTION 9

WINDOW Clause — Write DRY SQL with a Shared Window Definition

WINDOW ClauseDRY PrincipleMaintainabilityPostgreSQL/MySQL 8+/BigQuery

Background ▼

When several window functions repeat the same PARTITION BY ... ORDER BY ..., a WINDOW clause lets you define a named window once and reuse it, following the DRY: Don't Repeat Yourself principle.

SELECT
  ROW_NUMBER() OVER(w) AS rank,   -- Refer to the same window w
  SUM(sales)   OVER(w) AS running,
  LAG(sales)   OVER(w) AS prev
FROM t
WINDOW w AS (PARTITION BY emp_id ORDER BY month); -- Define it in one place

Why the WINDOW clause helps: Without it, the same window definition is copied once per function. A named window is more maintainable because changing the definition means editing only one place. The WINDOW clause goes after FROM, WHERE, and GROUP BY, and before ORDER BY.

Problem ▼

From the employee_sales table below, attach the following three metrics to every employee-month. Use a WINDOW clause to define PARTITION BY emp_id, dept ORDER BY month in one place.

rank: which numbered month this is for the employee, using ROW_NUMBER
running: the employee’s cumulative sales through that point, using SUM
prev_sales: the employee’s sales in the preceding month, using LAG; NULL for the first month

Input Table

▸ employee_sales

emp_id	dept	month	sales
E1	Sales	01	100
E1	Sales	02	120
E2	Sales	01	80
E2	Sales	02	90
E3	Tech	01	150

Expected Output ▼

emp_id	dept	month	sales	rank	running	prev_sales
E1	Sales	01	100	1	100	NULL
E1	Sales	02	120	2	220	100
E2	Sales	01	80	1	80	NULL
E2	Sales	02	90	2	170	80
E3	Tech	01	150	1	150	NULL

Model Answer ▼

SELECT
  emp_id,
  dept,
  month,
  sales,
  ROW_NUMBER() OVER w AS rank,       -- Refer to window w from the WINDOW clause with OVER w
  SUM(sales)   OVER w AS running,
  LAG(sales)   OVER w AS prev_sales

FROM employee_sales
WINDOW w AS (PARTITION BY emp_id, dept ORDER BY month)  -- Define shared window w once; future changes go here

ORDER BY dept, emp_id, month;

/*
  Execution order:
  1. FROM employee_sales (all 5 rows)
  2. Define named window w in the WINDOW clause
  3. Each SELECT function references OVER w and is evaluated over the same window
  4. ORDER BY dept, emp_id, month produces the final sorted output
  */

Explanation (table transitions & key points) ▼

SELECT
  emp_id, dept, month, sales,
  ROW_NUMBER() OVER w AS rank,
  SUM(sales) OVER w AS running,
  LAG(sales) OVER w AS prev_sales
FROM employee_sales
WINDOW w AS (PARTITION BY emp_id, dept ORDER BY month)
ORDER BY dept, emp_id, month;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM employee_salesLoad five rows. E1 and E2 belong to Sales, while E3 belongs to Tech. The WINDOW definition will create partitions next.

1 / 4

emp_id	dept	month	sales
E1	Sales	01	100
E1	Sales	02	120
E2	Sales	01	80
E2	Sales	02	90
E3	Tech	01	150

All 5 rows loaded

LEARNING POINTS

Lower maintenance cost:For example, changing PARTITION BY emp_id, dept to PARTITION BY emp_id would require editing three or more places without a WINDOW clause. A named window requires one change and prevents bugs caused by missing one copy. Production data-mart queries can contain more than ten window functions, making this difference substantial.

Extending a WINDOW clause:A named window can serve as a base to which an ORDER BY or frame is added. For example, define WINDOW base AS (PARTITION BY emp_id), then extend it as OVER (base ORDER BY month). This syntax is supported by PostgreSQL and some other databases.

◆ Code Comparison with and without WINDOW: Same Result, Different Maintainability

✗ Without WINDOW: copied 3 times

ROW_NUMBER() OVER(
  PARTITION BY emp_id, dept
  ORDER BY month) AS rank,
SUM(sales) OVER(
  PARTITION BY emp_id, dept  -- ← duplicate
  ORDER BY month) AS running,
LAG(sales) OVER(
  PARTITION BY emp_id, dept  -- ← duplicate
  ORDER BY month) AS prev_sales

✓ With WINDOW: defined once

ROW_NUMBER() OVER w AS rank,
SUM(sales)   OVER w AS running,
LAG(sales)   OVER w AS prev_sales
...
WINDOW w AS (
  PARTITION BY emp_id, dept  -- ← one place only
  ORDER BY month
)

ANTI-PATTERNS

Check database support:PostgreSQL, MySQL 8.0+, BigQuery, and DuckDB support the WINDOW clause, but SQL Server and Oracle do not provide the same standard WINDOW-clause syntax. Check your team’s database environment before using it. Repeating the window specification can be safer when portability is required.

QUESTION 10

Advanced Review — Implement Session Analysis with CTE + LAG + SUM

CTELAG + CASE WHENSession AnalysisCommon Production Pattern

Background ▼

Session analysis is a fundamental product-analytics technique for identifying each session, or sequence of continuous actions, in a user’s event log. Use LAG + CASE WHEN to detect the rule “start a new session when more than 30 minutes have elapsed since the preceding event,” then cumulatively SUM the flags to assign session numbers. A gap of exactly 30 minutes remains in the same session.

WITH flagged AS (
  SELECT ...,
    CASE WHEN LAG(ts_col) OVER(...) IS NULL
            OR (ts_col - LAG(ts_col) OVER(...)) > gap_limit
         THEN 1 ELSE 0 END AS is_new_session
  FROM table_name
)
SELECT ...,
  SUM(is_new_session) OVER(PARTITION BY key_col ORDER BY ts_col) AS session_id
FROM flagged;

Units of event_time: event_time is an integer number of minutes since midnight: 600 means 10:00 and 660 means 11:00. Time differences are therefore plain subtraction. In a production database, calculate them with a function or expression such as DATEDIFF or EXTRACT(EPOCH FROM ...), according to the database dialect.

Problem ▼

From the user_events table below, assign a session number as session_id for each user under this rule: start a new session when the gap from the preceding event exceeds 30 minutes, meaning the difference in event_time is greater than 30. Each user’s first event starts session 1.

Input Table (event_time is minutes since midnight: 600=10:00, 605=10:05, and so on)

▸ user_events

user_id	event_time	event_type
U1	600	login
U1	605	click
U1	645	click
U1	650	logout
U2	660	login
U2	670	click

Expected Output ▼

user_id	event_time	event_type	session_id
U1	600	login	1
U1	605	click	1
U1	645	click	2
U1	650	logout	2
U2	660	login	1
U2	670	click	1

Model Answer ▼

WITH gap_flagged AS (
  SELECT
    user_id,
    event_time,
    event_type,
    CASE                                                                                       -- Gap exceeds 30 minutes, or first row (NULL): new session = 1
      WHEN LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) IS NULL
        OR (event_time - LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time)) > 30
      THEN 1                                                                                   -- Session-start flag
      ELSE 0                                                                                   -- Within the same session
    END AS is_new_session

  FROM user_events
)

SELECT
  user_id,
  event_time,
  event_type,
  SUM(is_new_session) OVER(  -- Cumulative flags become session numbers: 1,0,1,0... → 1,1,2,2...
    PARTITION BY user_id
    ORDER BY event_time
  ) AS session_id

FROM gap_flagged
ORDER BY user_id, event_time;

/*
  Execution order:
  1. CTE gap_flagged
  2. Main query
  3. ORDER BY user_id, event_time produces the output
  */

Explanation (table transitions & key points) ▼

▼ ◇ STEP 1: CTE gap_flagged — Detect gaps with LAG and attach a session-start flag

WITH gap_flagged AS (
  SELECT
    user_id, event_time, event_type,
    CASE
      WHEN LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) IS NULL
      OR (event_time - LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time)) > 30
      THEN 1 ELSE 0 END AS is_new_session
  FROM user_events
)

LEGEND ▼

Rows read / loaded

① FROM user_events ▼

FROM user_events (events from U1 and U2 are mixed)Load six event-log rows. U1 has four rows and U2 has two. U1's 600→605 gap is 5 minutes and remains in the same session, while 605→645 is a 40-minute gap and starts a new session; the next step detects this distinction.

1 / 3

user_id	event_time	event_type
U1	600	login
U1	605	click
U1	645	click
U1	650	logout
U2	660	login
U2	670	click

All 6 rows loaded (U1: 4 rows, U2: 2 rows)

▼ ◇ STEP 2: Main Query — Finalize Session IDs with a Cumulative Sum of is_new_session

SELECT
  user_id, event_time, event_type,
  SUM(is_new_session) OVER(
    PARTITION BY user_id
    ORDER BY event_time
  ) AS session_id
FROM gap_flagged
ORDER BY user_id, event_time;

LEGEND ▼

Rows read / loaded

① FROM gap_flagged (CTE result) ▼

Load the CTE result with its is_new_session flagsUse the gap_flagged CTE result. U1's rows carry the is_new_session flags [1,0,1,0]. Their cumulative sum creates the session numbers.

1 / 3

user_id	event_time	event_type	is_new_session
U1	600	login	1
U1	605	click	0
U1	645	click	1
U1	650	logout	0
U2	660	login	1
U2	670	click	0

All 6 rows loaded with is_new_session flags

LEARNING POINTS

Sessionization is broadly reusable:This pattern applies whenever time-series events must be divided into meaningful groups, including web page-view analysis, app sessions, call-center records, and factory-machine activity logs. Parameterizing the 30-minute threshold makes it easy to adapt the rule to business requirements.

Remember the flag-then-cumulate pattern:The essence of this query is a two-stage process: first attach a 0/1 flag, then cumulatively SUM it. Beyond sessions, the same versatile pattern can assign a new group number whenever the event type changes or increment a counter whenever a specified condition occurs.

Analysis after sessionization:Once session_id is attached, a further GROUP BY user_id, session_id makes advanced metrics straightforward, including average events per session, session duration as MAX - MIN(event_time), and conversion rate by session.

⊞ Further Aggregations Enabled by Session Analysis with GROUP BY

Analysis	Additional query after session_id has been assigned
Average events per session	`COUNT(*) / COUNT(DISTINCT session_id) GROUP BY user_id`
Session duration in minutes	`MAX(event_time) - MIN(event_time) GROUP BY user_id, session_id`
Top 10 users by session count	`COUNT(DISTINCT session_id) GROUP BY user_id ORDER BY COUNT DESC LIMIT 10`
Rate of sessions ending in logout	Identify the last row of each session with `ROW_NUMBER() OVER(PARTITION BY user_id, session_id ORDER BY event_time DESC)`, then test `event_type = 'logout'`

ANTI-PATTERNS

Do not search for the preceding event with a naive self-join:Without a CTE and window function, a self-join that compares every event with all earlier events can grow to O(N²) comparisons. LAG avoids this pairwise search. The actual total cost still includes sorting for PARTITION BY user_id ORDER BY event_time and depends on indexes, clustering, and the database’s execution plan.

Omitting PARTITION BY mixes events from every user:If you write SUM(is_new_session) OVER(ORDER BY event_time), a session boundary for U1 also affects U2’s events. Always specify PARTITION BY user_id to maintain an independent counter for each user.