SQL Window Functions: The Patterns Worth Knowing in Oracle

Window functions are one of the highest-leverage features in modern SQL. They let you compute results across rows related to the current row without collapsing the result set the way GROUP BY does. Oracle has had them since 8i (1999) and they remain underused in real codebases.

This post walks through the patterns I actually use in production, with the syntax that matters.

The basic shape

A window function runs over a “window” of rows you define with the OVER clause:

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

This returns each row plus a customer-level total. No GROUP BY, no collapsing.

The OVER clause has three parts:

OVER (
  PARTITION BY <columns>           -- how to split the data into groups
  ORDER BY <columns>                -- how to order within each partition
  ROWS|RANGE BETWEEN ... AND ...    -- which rows form the window
)

Pattern 1: Running totals

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS running_total
FROM orders
ORDER BY order_date;

The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause is the explicit window. Without it, the default when ORDER BY is present is the same — but being explicit avoids surprises.

Pattern 2: Ranking

SELECT
  customer_id,
  amount,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS drnk,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
FROM orders;

Three functions, three behaviors with ties:

• RANK — ties share a rank, next rank skips (1, 1, 3)
• DENSE_RANK — ties share a rank, no gaps (1, 1, 2)
• ROW_NUMBER — unique sequential numbers, ties broken arbitrarily (1, 2, 3)

Pattern 3: Top N per group

Combine ROW_NUMBER with a CTE filter:

WITH ranked AS (
  SELECT
    customer_id, order_id, amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM   ranked
WHERE  rn <= 3;

This returns the top 3 orders by amount per customer. Without window functions you’d need a correlated subquery or a self-join — both slower and harder to read.

Pattern 4: Period-over-period comparisons

LAG and LEAD access the previous or next row:

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)            AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month)  AS delta
FROM monthly_revenue;

Much cleaner than the self-join most people reach for first.

Pattern 5: First and last values per group

SELECT
  customer_id,
  order_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

Pay attention to the explicit window — LAST_VALUE defaults to a window ending at the current row, which often isn’t what you want.

Pattern 6: Moving averages

SELECT
  trade_date,
  price,
  AVG(price) OVER (ORDER BY trade_date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM stock_prices;

7-day moving average. Adjust the window size as needed.

Pattern 7: Percentiles and distribution

SELECT
  product_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)
    OVER (PARTITION BY category) AS median_price_in_category
FROM products;

PERCENTILE_CONT interpolates between values; PERCENTILE_DISC returns an actual value present in the data.

ROWS vs RANGE

This catches a lot of people:

• ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — exactly 7 physical rows
• RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW — all rows within a 6-day window of the current row’s ORDER BY value

RANGE is logical; ROWS is physical. Use RANGE when the order-by column is a date/timestamp and gaps in the data matter. Use ROWS when you want a fixed row count.

When NOT to use window functions

• When a plain GROUP BY aggregate is what you actually want and you don’t need per-row detail.
• When the function would force Oracle to sort a huge dataset and a different approach (a CTE with GROUP BY, a pre-aggregated table) would be faster.

Most “is this query slow because of the window function?” cases turn out to be the underlying sort, not the function itself. Check the execution plan before assuming.

The one-line summary

Window functions let you keep all the rows while computing across groups. If you find yourself writing a self-join, a correlated subquery, or a GROUP BY followed by a JOIN back to the detail, ask whether a window function would be cleaner.

In real codebases, the answer is usually yes.