PARTITION BY — Grouping Within Windows

-- Running total per customer (not across all customers)
SELECT
  customer_id,
  order_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER(
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS customer_running_total
FROM orders
ORDER BY customer_id, order_date;

-- Result:
-- customer_id | order_id | order_date  | total_amount | customer_running_total
-- 101         | 1        | 2024-01-01  | 500          | 500
-- 101         | 5        | 2024-01-15  | 300          | 800   <-- resets per customer
-- 101         | 9        | 2024-02-01  | 700          | 1500
-- 102         | 2        | 2024-01-03  | 400          | 400   <-- restarts here
-- 102         | 7        | 2024-01-20  | 600          | 1000

-- Rank orders by amount within each customer
SELECT
  customer_id,
  order_id,
  total_amount,
  RANK() OVER(
    PARTITION BY customer_id
    ORDER BY total_amount DESC
  ) AS amount_rank_per_customer
FROM orders;

-- Each customer's order compared to their own average
SELECT
  customer_id,
  order_id,
  total_amount,
  ROUND(AVG(total_amount) OVER(PARTITION BY customer_id), 2) AS customer_avg,
  total_amount - ROUND(AVG(total_amount) OVER(PARTITION BY customer_id), 2) AS diff_from_avg
FROM orders
ORDER BY customer_id, order_id;

-- Each product's % contribution to its category's revenue
SELECT
  p.category,
  p.product_name,
  SUM(oi.quantity * oi.unit_price) AS product_revenue,
  SUM(SUM(oi.quantity * oi.unit_price)) OVER(
    PARTITION BY p.category
  ) AS category_total,
  ROUND(
    SUM(oi.quantity * oi.unit_price) * 100.0 /
    SUM(SUM(oi.quantity * oi.unit_price)) OVER(PARTITION BY p.category),
    2
  ) AS pct_of_category
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;