RANK ROW_NUMBER LAG Interview Questions

A window function computes something using related rows but does not collapse the result the way GROUP BY does. I still get one output row per input row, plus extra columns. ROW_NUMBER() gives 1,2,3 uniquely inside a partition. RANK() gives 1,1,3 if two people tie. DENSE_RANK() gives 1,1,2 — no gap. LAG and LEAD look at the previous or next row, which is how I would compute salary versus last month. A running total is SUM(amount) OVER (PARTITION BY user_id ORDER BY date ROWS UNBOUNDED PRECEDING). I use these for top-N per department, second-highest salary, and month-over-month change. The thing I always include is OVER (PARTITION BY ... ORDER BY ...).

On ties. ROW_NUMBER always unique 1,2,3 even if salaries match. RANK gives 1,1,3. DENSE_RANK gives 1,1,2. If they want top salary including ties I use RANK or DENSE_RANK. If I want exactly n rows I use ROW_NUMBER. I always state which. LAG looks at the previous row in the window — I use it for previous-day revenue, not for ranking. Mixing LAG and RANK is fine in one SELECT.

SUM(amount) OVER (PARTITION BY account ORDER BY date ROWS UNBOUNDED PRECEDING). I mention ROWS versus RANGE because RANGE with ties can include extra rows. Running total is the easiest window after rank. I would not use a correlated subquery summing all previous dates on a large table. I would index (account, date) to help the sort.

LAG(col, 1) is the previous row in the window. LEAD is the next. A self-join on date equals date minus one fails on weekends and missing days. LAG still gives the previous existing row, which may be what I want for 'last transaction'. I would say which definition I need. I prefer windows. Self-join is the old style and it is easier to fan out rows.

Not usually. WHERE runs before windows. I wrap the window in a CTE or subquery and filter rn = 1 outside. People writing WHERE ROW_NUMBER() = 1 will fail in most engines. That one sentence shows I know SELECT logical order: FROM, WHERE, GROUP, HAVING, WINDOW, ORDER. I would write the CTE in the interview rather than argue with the parser.