Window Functions

Code

-- Rank orders by amount per customer
SELECT customer_id, id, total,
       RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk
FROM orders;

-- Running total and moving average
SELECT created_at::date AS day,
       SUM(total) AS daily,
       SUM(SUM(total)) OVER (ORDER BY created_at::date) AS running,
       AVG(SUM(total)) OVER (
         ORDER BY created_at::date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d
FROM orders
GROUP BY created_at::date
ORDER BY day;

-- LAG and LEAD for period-over-period
SELECT day, daily,
       LAG(daily, 7) OVER (ORDER BY day) AS prev_week
FROM daily_stats;

Explanation

Window functions compute aggregates or rankings across a set of rows related to the current row, without collapsing the result set. PARTITION BY groups rows, ORDER BY defines the frame, and ROWS BETWEEN sets a sliding window. LAG/LEAD access neighboring rows for period-over-period analysis.

More PostgreSQL Snippets