Window Functions

function_name(args) OVER (
  PARTITION BY column1, column2   -- optional: divide into groups
  ORDER BY column3               -- optional: define row order
  ROWS BETWEEN ...               -- optional: frame specification
)

SELECT name, category, price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
  RANK()       OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

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

SELECT order_date,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY order_date) AS prev_day_revenue,
       revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS daily_change,
       LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue
FROM daily_sales;

SELECT order_date,
       revenue,
       -- Running total
       SUM(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       -- 7-day moving average
       AVG(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_sales;

SELECT name, revenue,
       NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM customers;