Let’s talk about an advanced SQL feature: window functions!

Normally SQL expressions only (et you refer to information in a single row.

SELECT CONCAT(firstname, ' ', lastname) as full_name
(firstname and lastname are 2 columns from the same row)

person 1, bald and with a worried expression: can I refer to other rows though? Like subtract the value in the previous row?

person 2, happy with curly hair: yes, with * window functions *

Window functions are SQL expressions that let you reference values in other rows. The syntax (explained on the next page!) is:

[expression] OVER ([window definition])

Example: use LAG() to find how long since the last sale

SELECT item,
      day - LAG(day) OVER (ORDER BY day)
FROM sales

sales:

query output:

They’re part of SELECT, so they happen after HAVING:

FROM + JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

(arrow pointing to SELECT)
happy little stick figure with curly hair: window functions are here!