Return the ID, category ID, and price of every product, plus the catalog-wide average price and the category average price on each row

The shape

Two window functions live in the same SELECT list with two different windows. AVG(price) OVER () computes the catalog-wide average once and replicates that single value onto every row. AVG(price) OVER (PARTITION BY category_id) computes a per-category average and replicates each category's value onto its own rows. Every product keeps its individual price and gets both averages alongside it.

Clause by clause

• SELECT id, category_id, price returns each product's identifier, category, and individual price.
• The two window columns are:

AVG(price) OVER () AS overall_avg_price,
AVG(price) OVER (PARTITION BY category_id) AS category_avg_price

The first window, OVER (), is empty: AVG runs across every row in the result, so overall_avg_price is the catalog-wide average, identical on every row. The second window, OVER (PARTITION BY category_id), splits the row set by category: AVG runs inside each category independently, so category_avg_price varies between categories but is the same for every row that shares a category_id.

• FROM products reads the catalog. Both averages are computed over the same source rows; the windows differ only in how they group those rows.

Why two separate windows in one query

The two numbers in the ratio analysis live at different scales: one is global, one is per-category. A single window can only define one scope at a time. To return both alongside each product, each scope needs its own OVER clause, and each OVER clause produces its own column. The fact that both functions are AVG(price) is incidental. What differs is the window definition.

The trap

Reading AVG(price) OVER () and AVG(price) OVER (PARTITION BY category_id) as variations of the same calculation is the easy mistake. They are not variations. They are two independent window functions that happen to share the same input column and the same aggregate. Each OVER (...) defines its own window, computes its own values, and produces its own output column. Swapping the order in the SELECT list, or merging them, would change the report. Each window stands on its own.