Window Functions

OVER()

Every window function MUST have this, which defines the window of rows used for calculations.

PARTITION BY

Groups Data in a window. Similar to GROUP BY, but does not collapse rows, instead, it resets calculations for each partition

SELECT

region,

date,

revenue,

SUM(revenue) OVER (PARTITION BY region ORDER BY date) AS running_total

FROM sales;

ORDER BY

Defines row order in the window. Just like normal ORDER BY but inside of a window function, it controls how rows are processed within the window

SELECT
 title,
 box_office,
 RANK() OVER (ORDER BY box_office DESC) AS rank
FROM movies;

ROWS BETWEEN

Defines row scope. Controls which rows are included in each calculation

PRECEDING, CURRENT ROW, FOLLOWING

• (n) PRECEDING —> Includes the last n rows

• CURRENT ROW —> Includes the row itself

• (n) FOLLOWING —> Includes the next X rows

SELECT
 date,
 revenue,
 AVG(revenue) OVER (
  ORDER BY date
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
 ) AS moving_avg
FROM sales;

UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING

• UNBOUNDED PRECEDING —> Starts from the first row

• UNBOUNDED FOLLOWING —> Goes up to the last row

SELECT
 date,
 revenue,
 SUM(revenue) OVER (
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS running_total
FROM sales;

RANGE

Uses value-based ranges. Must include an INTERVAL keyword

SELECT
 date,
 revenue,
 SUM(revenue) OVER (
  ORDER BY date
  RANGE BETWEEN INTERVAL '1 month' PRECEDING AND CURRENT ROW
 ) AS total_sales_this_month
FROM sales;

LAG(column, offset, default) OVER (PARTITION BY… ORDER BY…)

Return the value of a column from a previous row in the result set, based on the ORDER BY clause, without collapsing rows.

• offset: how many rows behind (default is 1)

• default: what to return if the lag value doesn’t exist (optional)

LEAD(column, offset, default) OVER (PARTITION BY… ORDER BY…)

Returns the value of a column from a future row in the result set, based on the ORDER BY clause, without collapsing rows

• offset: how many rows ahead (default is 1)

• default: value to return if no next row exists (optional)

FIRST_VALUE(column)

Returns the first value in the table or partition

LAST_VALUE(column)

Returns the last value in the table or partition

NTILE(n)

Splits the data into n approximately equal pages