Interview Q&A / PostgreSQL

1. How is a window function different from GROUP BY?

   GROUP BY collapses rows into one row per group. A window function computes over a related set of rows but keeps every original row, so you can show a row's value beside its group's total or its rank.

   BeginnerQuick answer1 min read
2. What do LAG and LEAD do, and how would you use them to find day-over-day change?

   LAG reads a column from a previous row in the window order and LEAD from a following row. Subtracting LAG from the current value gives the change from the previous row, without a self-join.

   BeginnerQuick answer1 min read
3. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

   All number rows in a window order. ROW_NUMBER gives unique numbers with no ties, RANK gives ties the same number and then skips ahead, and DENSE_RANK gives ties the same number without skipping.

   BeginnerQuick answer1 min read
4. How do you compute a running total, and what is a window frame?

   Use sum(x) OVER (ORDER BY ...). The frame decides which rows around the current one are included; with ORDER BY the default is from the start up to the current row (including peers with equal values), which gives a running total.

   IntermediateQuick answer1 min read
5. How do you get the top 2 highest-paid employees in each department?

   Number the rows within each department using ROW_NUMBER or DENSE_RANK with PARTITION BY dept ORDER BY salary DESC, then filter on that number in an outer query. A lateral join is an alternative.

   IntermediateQuick answer1 min read