Window functions

GROUP BY returns one row per group. A window function calculates over the group and keeps every row. The window is defined with OVER (PARTITION BY … ORDER BY …).

ROW_NUMBER, RANK and DENSE_RANK

All three number the rows within the partition; the difference is in ties. Orders 102 and 110 are worth 900.

SELECT id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS row_number,
       RANK()       OVER (ORDER BY amount DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM orders
WHERE amount >= 180
ORDER BY amount DESC, id;

id  | amount | row_number | rank | dense_rank
--- | ------ | ---------- | ---- | ----------
105 | 1050   | 1          | 1    | 1
102 | 900    | 2          | 2    | 2
110 | 900    | 3          | 2    | 2
101 | 230    | 4          | 4    | 3
104 | 180    | 5          | 5    | 4

• ROW_NUMBER: always 1, 2, 3… (in a tie, the tiebreaker you give decides, or chance).
• RANK: a tie shares the position and skips the next one (1, 2, 2, 4).
• DENSE_RANK: a tie shares the position without skipping (1, 2, 2, 3).

The largest order of each customer

The "top N per group" pattern: number the rows within the partition and filter outside (WHERE runs before the window, so the filter goes in a CTE or subquery).

WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, id) AS n
  FROM orders
  WHERE amount IS NOT NULL
)
SELECT customer_id, id, amount FROM ranked WHERE n = 1 ORDER BY customer_id;

customer_id | id  | amount
----------- | --- | ------
1           | 110 | 900
2           | 102 | 900
3           | 104 | 180
4           | 105 | 1050
5           | 107 | 30
9           | 109 | 80

Running total

With an ORDER BY inside the OVER, the sum becomes a running total. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: rows tied in the ordering enter together. To sum row by row, say ROWS.

SELECT order_date, SUM(amount) AS per_day,
       SUM(SUM(amount)) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status = 'delivered'
GROUP BY order_date
ORDER BY order_date;

order_date | per_day | running_total
---------- | ------- | -------------
2024-06-01 | 1130    | 1130
2024-06-02 | 180     | 1310
2024-06-03 | 1050    | 2360
2024-06-04 | 180     | 2540
2024-06-05 | 980     | 3520

LAG and LEAD: the neighboring row

SELECT id, order_date, amount,
       LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date, id) AS previous,
       amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date, id) AS difference
FROM orders
WHERE customer_id = 1
ORDER BY order_date, id;

id  | order_date | amount | previous | difference
--- | ---------- | ------ | -------- | ----------
101 | 2024-06-01 | 230    | NULL     | NULL
103 | 2024-06-02 | 80     | 230      | -150
110 | 2024-06-05 | 900    | 80       | 820

The first row has no previous one: LAG returns NULL (or the third argument, if you give a default).

• In PySpark: from pyspark.sql import Window and F.row_number().over(Window.partitionBy("customer_id").orderBy(F.desc("amount"))).
• A window without PARTITION BY sends everything to a single partition: in Spark, that is a single task processing the whole table.