Module lessons (2/4)
Grouping with PARTITION BY
Grouping with PARTITION BY
Having global calculations on every row is handy, but often it is not enough. We may want to compute the average per category or per customer, while keeping the original detail on every row.
This is where PARTITION BY inside the OVER(...) clause comes in.
How PARTITION BY works
Conceptually it behaves similarly to GROUP BY, but instead of collapsing rows in the result, it only defines the boundary of the "window" of data on which the function will operate to compute the result for the single row.
SELECT
product_name,
category_id,
price,
AVG(price) OVER(PARTITION BY category_id) AS cat_avg_price
FROM products;Each product row keeps its own details, and the cat_avg_price column will show the average limited to the products in its same category.
From the 'products' table, extract 'id', 'category_id' and 'price'. Add a 'category_total' column computing the SUM of 'price' for each 'category_id' using window functions.
Show hint
Use SUM(price) OVER(PARTITION BY category_id).
Solution available after 3 attempts
From the 'reviews' table, show 'product_id', 'rating' and 'avg_product_rating' (the average rating partitioned by product). Show all rows.
Show hint
Use AVG(rating) and PARTITION BY product_id. Cast it to NUMERIC if needed.
Solution available after 3 attempts