Ranking and offsets

Two families cover most real work:

Ranking — ROW_NUMBER, RANK, DENSE_RANK:

SELECT customer_id, amount,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS r
FROM orders;

Offsets — LAG and LEAD read a neighboring row:

SELECT order_date, amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS delta
FROM orders;

PARTITION BY restarts the window per group; ORDER BY inside the window defines "previous". Together they answer "compared to last time" questions in one pass.