Window Functions
The single concept that separates juniors from seniors in interviews.
LAG, LEAD, FIRST_VALUE: peeking at the row before or after
Comparing a row to its neighbor used to mean joining a table to itself. LAG and LEAD let a row see the previous or next row directly, in one line.
Frame clauses: ROWS vs RANGE, the setting that decides what a window function sees
Every window function looks at a 'frame', a slice of nearby rows. ROWS counts physical rows; RANGE counts by value. They usually agree, until there are duplicate values.
Pivot & unpivot: turning rows into columns, and back again
A pivot turns a long list of transactions into a spreadsheet-style grid. Unpivot does the reverse. Neither is a keyword in Postgres, both are patterns you build by hand.
ROW_NUMBER, RANK, DENSE_RANK: three ways to number rows within a group
All three give each row a number within a group. They only disagree on ties, and that disagreement is the whole interview question.
Running totals & moving averages: the aggregates you know, with OVER added
SUM and AVG become cumulative or rolling totals the moment you add OVER. The difference between the two is one word in the frame.
CASE inside an aggregate: counting and summing 'if'
Wrapping CASE inside SUM or COUNT lets you compute several different totals, one per condition, in a single row. It's the SQL version of a pivot table.