Introduction and the OVER clause

Introduction and the OVER Clause

In the previous lessons we saw how to use GROUP BY to aggregate data. However, GROUP BY has a huge limitation: it collapses rows. If you group by city, you get a single row per city and lose the details of the individual customers.

This is where Window Functions come into play. They let you run aggregate calculations (such as sums or averages) while keeping the original rows.

The OVER() clause

The magic keyword is OVER(). It tells the database that the aggregate function (e.g. SUM, AVG, COUNT) should be treated as a window function, computed over the whole result set (the global "window") but applied and returned on every single row.

SELECT
  order_id,
  total_amount,
  AVG(total_amount) OVER() AS global_average
FROM orders;

In this example, we keep every individual order, but every row also has a global_average column with the average of all orders. Very useful, for instance, to measure how much an order deviates from the average!