SQL window functions cheat sheet

SELECT name, price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
  RANK()       OVER (ORDER BY price DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY price DESC) AS drnk
FROM products;

SELECT * FROM (
  SELECT category, name, price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) WHERE rn <= 2;

SELECT order_date,
  SUM(1) OVER (ORDER BY order_date) AS orders_so_far
FROM orders;

SELECT customer_id, order_date,
  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order
FROM orders;

SELECT id, price,
  AVG(price) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM products;