A window function allows us to calculate across a set of rows related to the current row.
-- MAX aggregate function
SELECT MAX(price) FROM products;
max
---------
2999.99
-- MAX window function
-- It returns the maximum price of the products table,
-- **doesn't group rows** but retains individual rows.
-- The OVER clause makes the MAX function a window function.
SELECT
product_name,
price,
MAX(price) OVER ()
FROM
products
ORDER BY
price DESC;
product_name | price | max
----------------------------+---------+---------
Samsung QN900C Neo QLED | 2999.99 | 2999.99
LG G3 OLED | 2499.99 | 2999.99
Sony Bravia XR A95K | 2499.99 | 2999.99
LG OLED TV C3 | 1999.99 | 2999.99
Samsung Galaxy Z Fold 5 | 1799.99 | 2999.99
Lenovo ThinkPad X1 Carbon | 1599.99 | 2999.99
...
Syntax
function_name (expression) OVER (
[PARTITION BY expression_list]
[ORDER BY expression_list]
[frame_clause]
)
expression: The column or expression you want the window function to calculate.PARTITION BY: This groups the rows into partitions.ORDER BY: This defines the order of partitions.frame_clause: This defines the subset of rows within the partition.A window frame defines which rows relative to the current row are used in the calculation. Only some window functions can use frames:
| Window Function Type | Uses frame? | Example |
|---|---|---|
| Aggregate window | ✅ Yes | SUM, AVG, MAX |
| Ranking | ❌ No | ROW_NUMBER, RANK |
| Offset functions | ❌ No | LAG, LEAD |
Syntax:
ROWS BETWEEN <start> AND <end>
-- example
SUM(amount) OVER (
ORDER BY visited_on
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
Practice:
Practice:
If multiple rows share the same rank, the next rank skips numbers.