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

When ORDER BY is present, the default frame—normally RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—always includes the first row, which has the highest score, so every current row receives that top value. If scores tie, rows with the same ordering value are peers in the same frame, but the highest score returned by FIRST_VALUE does not change.

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.

Every row in category A has top_score 92 from P2, while every row in category B has top_score 88 from P5. A row with gap=0 is the leader of its category.

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

Because the frame slides along the ordered rows, it automatically continues to reference the latest N records as data grows. This differs fundamentally from a cumulative average, which averages every row from the beginning through the current row.

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.

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

Without a WINDOW clause, the same query would copy and paste PARTITION BY emp_id ORDER BY month three times. A named window is more maintainable because changing the definition requires editing only one place.

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.

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

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 gap_flagged AS (
  SELECT ...,
    CASE WHEN LAG(event_time) OVER(...) IS NULL
            OR (event_time - LAG(event_time) OVER(...)) > 30
         THEN 1 ELSE 0 END AS is_new_session
  FROM user_events
)
SELECT ...,
  SUM(is_new_session) OVER(PARTITION BY user_id ORDER BY event_time) AS session_id
FROM gap_flagged;

event_time is represented as an integer number of minutes since midnight: for example, 600 means 10:00 and 660 means 11:00. In a production database, calculate the time difference with a function or expression such as DATEDIFF or EXTRACT(EPOCH FROM ...), according to the database dialect.

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.

U1: 600→605 (gap 5 min) →645 (gap 40 min → new session) →650 (gap 5 min) / U2: 660→670 (gap 10 min)