Window functions let you precisely specify the range of rows to aggregate, called the frame. Here, you will use a frame to calculate the moving average familiar from stock-price charts and other time-series analysis.

AVG(amount) OVER(
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Using the daily_sales table, return each date's sales and the moving average over the latest three rows (from two rows back through the current row).

For 04-01, the average is (100)/1=100; for 04-02, (100+140)/2=120; for 04-03, (100+140+120)/3=120; and for 04-04, (140+120+190)/3=150.

SELECT
  date,
  amount,
  AVG(amount) OVER(  -- moving average without collapsing rows
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- latest 3 rows: current row + previous 2
  ) AS moving_avg_3d
FROM daily_sales
ORDER BY date;

/*
  Calculation:
  04-01: no previous row, so average [04-01] → 100
  04-02: one previous row, so average [04-01, 04-02] → (100+140)/2 = 120
  04-03: two previous rows, so average [04-01, 04-02, 04-03] → 360/3 = 120
  04-04: average [04-02, 04-03, 04-04] → (140+120+190)/3 = 150
*/

Because of SQL's logical execution order, you cannot put a window function directly in a WHERE clause. To filter rows by its result, run the window function inside a subquery (or CTE), then filter in the outer query's WHERE clause.

-- A highly reusable pattern for retrieving the latest history row
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC, log_id DESC) AS rn
  FROM logs
) AS tmp
WHERE rn = 1;

From the orders table below, retrieve every column from only the latest order row for each user: the row with the newest order_date. If dates tie, choose the row with the greatest order_id.

SELECT
  order_id,
  user_id,
  order_date,
  item
FROM (
  -- Subquery: number each user's orders from newest to oldest
  SELECT
    order_id,
    user_id,
    order_date,
    item,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY order_date DESC, order_id DESC
    ) AS rn
  FROM orders
) AS tmp    -- a subquery requires an alias (temporary name)
WHERE rn = 1 -- outer query keeps only the single latest row
ORDER BY user_id;

/*
  Execution order:
  1. FROM orders (inner query)
  2. Window function (inner query): build a temporary table (tmp) with ROW_NUMBER
     U1: Laptop(rn=1), Mouse(rn=2)
     U2: Monitor(rn=1), Desk(rn=2)
     U3: Chair(rn=1)
  3. FROM tmp (outer query)
  4. WHERE rn = 1: keep only matching rows in the outer query
  5. SELECT produces the output
*/

Extending Q8's latest-row pattern, you can change the filter to something like rn <= 3 to retrieve the top N rows in each group. When ties should share a rank, use RANK and filter the top N ranks instead. This is another powerful window-function operation that GROUP BY cannot perform.

From the products table below, retrieve the products in the top two price ranks within each category.

Products with the same price must share a rank and all be included. Output the rank (rnk) as well, so a tie can produce more than two products in a category.

SELECT
  category,
  name,
  price,
  rnk
FROM (
  SELECT
    category,
    name,
    price,
    RANK() OVER(  -- rank by descending price within each category
      PARTITION BY category
      ORDER BY price DESC
    ) AS rnk
  FROM products
) AS tmp
WHERE rnk <= 2  -- keep top 2 ranks; ties may yield more than 2 rows
ORDER BY category, rnk;

/*
  Execution order:
  1. In the subquery, assign RANK by descending price within each category
  2. In the outer query, filter on rnk <= 2 (the top two ranks)
  */

Production dashboards often place several aggregate metrics side by side in one query.
Bring together everything you have learned and use one SELECT to calculate current-month sales, running sales, previous-month sales, and the change from the previous month.

From the monthly_sales table below, output these four values for each month:
1. amount (current-month sales)
2. running_total (running sales from January)
3. prev_amount (previous-month sales; January may be NULL)
4. diff_from_prev (change in sales from the previous month).

SELECT
  month,
  amount,
  SUM(amount) OVER(ORDER BY month) AS running_total,           -- ① running sales with an ordered SUM
  LAG(amount) OVER(ORDER BY month) AS prev_amount,             -- ② previous-month sales from the prior row
  amount - LAG(amount) OVER(ORDER BY month) AS diff_from_prev  -- ③ current-month sales - previous-month sales

FROM monthly_sales
ORDER BY month;

/*
  Execution order:
  1. FROM monthly_sales
  2. Evaluate the window-function expressions in SELECT
  3. ORDER BY sorts the output
  */