How to Use Window Functions in Redshift

How do I use window functions in Amazon Redshift?

Redshift window functions add ranking, running totals, and moving averages without collapsing rows.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose window functions instead of GROUP BY?

Window functions keep every row visible while adding aggregated metrics like rankings or running totals. GROUP BY collapses rows and hides detail.

What is the basic window-function syntax?

Use function_name(arg) OVER (PARTITION BY expr ORDER BY expr ROWS BETWEEN frame). Only the OVER clause is mandatory; other clauses are optional.

How do PARTITION BY and ORDER BY interact?

PARTITION BY resets calculations for each group, while ORDER BY sets the sequence inside each partition for cumulative logic.

Example: rank customers by total spend

RANK() OVER (ORDER BY SUM(total_amount) DESC) ranks customers after aggregating their spend.

Can I calculate running totals?

Yes—use a frame like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to sum all prior rows up to the current one.

Example: daily cumulative revenue

SUM(total_amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

How to mix window and regular columns?

You can reference any column from the FROM clause; no need to add it to GROUP BY.

Best practices for performance?

Create sort keys that match the ORDER BY inside large window frames, minimize partitions, and avoid DISTINCT inside window subqueries.

Why How to Use Window Functions in Redshift is important

How to Use Window Functions in Redshift Example Usage


-- Running 7-day moving average of daily revenue
WITH daily_revenue AS (
  SELECT order_date, SUM(total_amount) AS day_revenue
  FROM Orders
  GROUP BY order_date
)
SELECT
  order_date,
  day_revenue,
  AVG(day_revenue) OVER (
      ORDER BY order_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_revenue
ORDER BY order_date;

How to Use Window Functions in Redshift Syntax


-- General form
SELECT
    column_list,
    window_function(arg) OVER (
        [PARTITION BY partition_expression]
        [ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]
        [ROWS|RANGE frame_spec]
    ) AS alias
FROM table_name;

-- Ecommerce rank example
SELECT
    c.id,
    c.name,
    SUM(o.total_amount) AS lifetime_value,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spend_rank
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Common Mistakes

Using ORDER BY without PARTITION BY for unrelated rows: this ranks the whole table, not per customer. Fix by adding PARTITION BY customer_id.
Omitting a frame clause for moving averages: default frame is the entire partition, giving wrong results. Specify ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.