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;