function_name(column) OVER (
  [PARTITION BY partition_columns]
  [ORDER BY sort_columns]
  [ROWS | RANGE BETWEEN frame_start AND frame_end]
)

-- OVER() is what makes it a window function
-- PARTITION BY splits rows into independent windows
-- ORDER BY controls the order within each window
-- ROWS/RANGE defines the frame (which rows to include in the calculation)

-- GROUP BY aggregate: collapses rows, one row per customer
SELECT customer_id, SUM(total) AS total_spent
FROM orders GROUP BY customer_id;

-- Window function: keeps all rows, adds the total alongside each row
SELECT
  order_id,
  customer_id,
  total,
  SUM(total) OVER (PARTITION BY customer_id) AS customer_total_spent
FROM orders
WHERE status = 'delivered';

-- Per-category stats alongside each product row
SELECT
  product_id,
  name,
  category_id,
  price,
  COUNT(*)   OVER (PARTITION BY category_id)    AS products_in_category,
  AVG(price) OVER (PARTITION BY category_id)    AS category_avg_price,
  MAX(price) OVER (PARTITION BY category_id)    AS category_max_price,
  price - AVG(price) OVER (PARTITION BY category_id) AS price_vs_category_avg
FROM products
WHERE is_active = 1
ORDER BY category_id, price DESC;

-- Number rows within each customer's orders by date
SELECT
  order_id,
  customer_id,
  total,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS order_sequence
FROM orders
WHERE status = 'delivered'
ORDER BY customer_id, order_sequence;

-- Get only the first order per customer (using ROW_NUMBER in a subquery)
SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS rn
  FROM orders
  WHERE status = 'delivered'
) AS ranked
WHERE rn = 1;

SELECT
  product_id,
  name,
  category_id,
  price,
  RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rank_in_cat,
  DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS dense_rank_in_cat,
  ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num_in_cat
FROM products
WHERE is_active = 1
ORDER BY category_id, price DESC;

-- Top 3 products by revenue per category (using DENSE_RANK)
SELECT * FROM (
  SELECT
    cat.name         AS category,
    p.name           AS product,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    DENSE_RANK() OVER (
      PARTITION BY p.category_id
      ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS revenue_rank
  FROM order_items AS oi
  JOIN products    AS p   ON oi.product_id = p.product_id
  JOIN categories  AS cat ON p.category_id = cat.category_id
  JOIN orders      AS o   ON oi.order_id   = o.order_id
  WHERE o.status = 'delivered'
  GROUP BY p.category_id, cat.name, p.product_id, p.name
) AS ranked
WHERE revenue_rank <= 3
ORDER BY category, revenue_rank;

-- Divide customers into 4 spending quartiles
SELECT
  customer_id,
  ROUND(SUM(total), 2)     AS total_spent,
  NTILE(4) OVER (ORDER BY SUM(total) DESC) AS spending_quartile
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
ORDER BY total_spent DESC;

-- Label the quartiles
SELECT
  customer_id,
  total_spent,
  CASE spending_quartile
    WHEN 1 THEN 'Top 25%'
    WHEN 2 THEN 'Upper-middle 25%'
    WHEN 3 THEN 'Lower-middle 25%'
    WHEN 4 THEN 'Bottom 25%'
  END AS spending_tier
FROM (
  SELECT
    customer_id,
    ROUND(SUM(total), 2)                            AS total_spent,
    NTILE(4) OVER (ORDER BY SUM(total) DESC)        AS spending_quartile
  FROM orders WHERE status = 'delivered'
  GROUP BY customer_id
) AS tiers
ORDER BY total_spent DESC;

-- Month-over-month revenue comparison
WITH monthly AS (
  SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY DATE_FORMAT(created_at, '%Y-%m')
)
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(revenue - LAG(revenue, 1, 0) OVER (ORDER BY month), 2) AS growth,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0 /
    NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
  1) AS growth_pct
FROM monthly
ORDER BY month;

-- Show each order alongside the next order date for that customer
SELECT
  order_id,
  customer_id,
  created_at,
  LEAD(created_at) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS next_order_date
FROM orders
WHERE status = 'delivered'
ORDER BY customer_id, created_at;

-- For each order, show the customer's very first and most recent order total
SELECT
  order_id,
  customer_id,
  total,
  created_at,
  FIRST_VALUE(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_order_total,
  LAST_VALUE(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_total
FROM orders
WHERE status = 'delivered'
ORDER BY customer_id, created_at;

-- Cumulative revenue over time (running total)
SELECT
  DATE(created_at)  AS order_date,
  total,
  SUM(total) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                    AS cumulative_revenue
FROM orders
WHERE status = 'delivered'
ORDER BY created_at;

-- 7-day rolling average of daily revenue
WITH daily_revenue AS (
  SELECT
    DATE(created_at) AS order_date,
    ROUND(SUM(total), 2) AS daily_total
  FROM orders
  WHERE status = 'delivered'
  GROUP BY DATE(created_at)
)
SELECT
  order_date,
  daily_total,
  ROUND(AVG(daily_total) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS rolling_7day_avg
FROM daily_revenue
ORDER BY order_date;

-- ROWS frame: exactly 3 preceding rows
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

-- RANGE frame: all rows with the same ORDER BY value as current row
SUM(total) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW)
-- MySQL 8.0.2+ supports RANGE with date/time intervals

-- Common frame shortcuts
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- cumulative
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- entire partition
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING           -- 3-row centered average

-- Customer lifetime value with ranking and percentile
SELECT
  c.customer_id,
  c.first_name,
  c.country,
  ROUND(SUM(o.total), 2)                          AS lifetime_value,
  COUNT(DISTINCT o.order_id)                      AS order_count,
  RANK() OVER (ORDER BY SUM(o.total) DESC)        AS global_rank,
  RANK() OVER (
    PARTITION BY c.country
    ORDER BY SUM(o.total) DESC
  )                                               AS country_rank,
  ROUND(
    PERCENT_RANK() OVER (ORDER BY SUM(o.total)) * 100,
  1)                                              AS percentile
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.first_name, c.country
ORDER BY lifetime_value DESC
LIMIT 50;