Topic 44 of 52
OVER PARTITION BY
Overview
The OVER clause defines the window (set of rows) that a window function operates on. PARTITION BY divides rows into independent groups; ORDER BY defines the row order within each group; ROWS/RANGE specifies the exact frame of rows included.
Syntax
sql
-- OVER with PARTITION BY (window per group)
SUM(amount) OVER (PARTITION BY category_id)
-- Computes sum per category, but each row keeps its own data
-- OVER with ORDER BY (running calculation)
SUM(amount) OVER (ORDER BY date)
-- Running total over all rows, ordered by date
-- OVER with both (running total per partition)
SUM(amount) OVER (PARTITION BY user_id ORDER BY date)
-- Running total per user, ordered by date
-- Frame specification
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) -- default: running total from beginning of partition
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) -- 7-day rolling sum
SUM(amount) OVER (
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) -- same value = total across ALL rowsCommon Pitfalls
- Omitting ORDER BY in OVER means the window includes all rows in the partition — useful for computing totals, but may not be what you intend.
- ROWS BETWEEN vs RANGE BETWEEN: ROWS counts physical rows, RANGE groups rows with equal ORDER BY values together.
- Interview tip: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a 7-day rolling window — a common pattern in time-series analytics.
Real-World Example
7-day rolling average revenue and cumulative totals:
example
sql
WITH daily_revenue AS (
SELECT
DATE(created_at) AS day,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at)
)
SELECT
day,
revenue,
-- Running total (cumulative) for the month
SUM(revenue) OVER (
PARTITION BY DATE_TRUNC('month', day)
ORDER BY day
ROWS UNBOUNDED PRECEDING
) AS monthly_cumulative,
-- 7-day rolling average
ROUND(AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2) AS rolling_7day_avg,
-- Compare to same day last week
LAG(revenue, 7) OVER (ORDER BY day) AS same_day_last_week,
-- Percentage of monthly total
ROUND(100.0 * revenue / SUM(revenue) OVER (
PARTITION BY DATE_TRUNC('month', day)
), 2) AS pct_of_month
FROM daily_revenue
ORDER BY day;