Window Functions (OVER, PARTITION BY)

-- ROW_NUMBER (sequential numbering)
SELECT 
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM products;

-- RANK (with gaps for ties)
SELECT 
  name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank
FROM products;

-- DENSE_RANK (no gaps for ties)
SELECT 
  name,
  price,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

-- PARTITION BY (separate ranking per group)
SELECT 
  category,
  name,
  price,
  RANK() OVER (
    PARTITION BY category 
    ORDER BY price DESC
  ) AS rank_in_category
FROM products;

-- Top N per group
WITH ranked AS (
  SELECT 
    category,
    name,
    price,
    ROW_NUMBER() OVER (
      PARTITION BY category 
      ORDER BY price DESC
    ) AS rn
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE rn <= 3;

-- LAG (previous row value)
SELECT 
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- LEAD (next row value)
SELECT 
  date,
  revenue,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_sales;

-- Running total (SUM window function)
SELECT 
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

-- Moving average
SELECT 
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date 
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7day
FROM daily_sales;

-- FIRST_VALUE and LAST_VALUE
SELECT 
  date,
  revenue,
  FIRST_VALUE(revenue) OVER (
    ORDER BY date
  ) AS first_day_revenue,
  LAST_VALUE(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_day_revenue
FROM daily_sales;

-- Multiple window functions
SELECT 
  name,
  category,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS overall_rank,
  ROW_NUMBER() OVER (
    PARTITION BY category 
    ORDER BY price DESC
  ) AS category_rank,
  price - AVG(price) OVER (
    PARTITION BY category
  ) AS price_diff_from_avg
FROM products;

-- Percentile ranking
SELECT 
  name,
  price,
  PERCENT_RANK() OVER (ORDER BY price) AS percentile
FROM products;