COUNT() is also frequently used as a window function. It can attach group-level counts and totals, such as a user's total number of orders or total order amount, to every detail row without collapsing those rows.

SELECT
  order_id, user_id,
  COUNT(*) OVER(PARTITION BY user_id) AS user_order_count
FROM orders;

From the orders table below, output the order ID, user ID, and amount, together with that user's total number of orders (order_count) and total order amount (total_amount).

SELECT
  order_id,
  user_id,
  amount,
  COUNT(*) OVER(PARTITION BY user_id) AS order_count,  -- Number of orders for each user
  SUM(amount) OVER(PARTITION BY user_id) AS total_amount
FROM orders                                               -- Calculate the total amount for each user
ORDER BY user_id, order_id;

/*
  Execution order:
  1. FROM orders             → Retrieve 5 rows
  2. PARTITION BY user_id    → Split the rows by user
  3. COUNT(*) and SUM(amount) → Calculate and attach both aggregates
  4. SELECT                  → Sort by user_id and order_id
  */

FIRST_VALUE(column) returns the value from the first row in a specified ordering. It is particularly useful when you want to attach facts such as a customer's first purchase date or first page viewed to every row in that customer's history.

SELECT
  user_id, event_date,
  FIRST_VALUE(event_name) OVER(
    PARTITION BY user_id
    ORDER BY event_date ASC
  ) AS first_event
FROM events;

For every event in the user_events table below, attach the first event performed by that user as first_event.

SELECT
  user_id,
  event_date,
  event_name,
  FIRST_VALUE(event_name) OVER(
    PARTITION BY user_id
    ORDER BY event_date ASC
  ) AS first_event
FROM user_events                 -- Order each user by date and retrieve the group's first event_name
ORDER BY user_id, event_date;

/*
  Execution order:
  1. FROM user_events
  2. PARTITION BY user_id splits the rows into groups
  3. ORDER BY event_date ASC orders each group by date ascending
  4. FIRST_VALUE() attaches Registration from U1's first row (04-01) to every U1 row
  5. SELECT outputs the result
*/

Whereas LAG() retrieves a preceding row, LEAD() retrieves the next row: data later in the specified ordering. Bringing a user's next visit date onto the current row makes it possible to calculate the revisit interval, such as how many days passed before the user returned.

From the website_visits table below, retrieve each user's visit date (visit_date) and that user's next visit date (next_visit).

If there is no next visit because the row is last in its partition, return NULL.

SELECT
  user_id,
  visit_date,
  LEAD(visit_date) OVER(
    PARTITION BY user_id
    ORDER BY visit_date ASC
  ) AS next_visit
FROM website_visits            -- Order each user by date and retrieve visit_date from the next row
ORDER BY user_id, visit_date;

/*
  Execution order:
  1. FROM website_visits
  2. PARTITION BY user_id splits the rows into groups
  3. ORDER BY visit_date ASC orders each group by date ascending
  4. LEAD() retrieves visit_date from the next row in that ordering
  5. SELECT outputs the result
  */

LAG() and LEAD() return NULL by default when the target row does not exist. If that NULL is used in arithmetic such as subtraction, the result is also NULL.
To avoid this, you can provide a third argument to the function as its default value.

-- LAG(column, row offset, default value)
LAG(amount, 1, 0) OVER(ORDER BY date)

From the daily_metrics table below, retrieve the user count (users) for each date and the preceding row's user count in date order (prev_users), which is the previous day in this example.
For the first row, where no preceding row exists, return 0 instead of NULL.

SELECT
  date,
  users,
  LAG(users, 1, 0) OVER(ORDER BY date) AS prev_users  -- users from 1 row earlier, or 0 if no such row exists
FROM daily_metrics
ORDER BY date;

/*
  Execution order:
  1. FROM daily_metrics  → Retrieve the rows
  2. ORDER BY date       → Define the window order by date ascending
  3. LAG(users, 1, 0)    → Attach the preceding value (0 for the first row)
  4. SELECT              → Output the columns
  */