SQL & PostgreSQL
Joins, window functions, transactions, and query optimization.
GROUP BY vs a window function?
GROUP BY collapses source rows into one row per group, while window functions calculate across related rows without removing the individual rows. A window is defined by partitions, ordering and sometimes a frame. This makes windows useful when a report needs both detail and comparisons such as department rank.
ROW_NUMBER vs RANK vs DENSE_RANK?
These window functions assign positions within an ordered partition. ROWNUMBER gives each row a different number, RANK leaves gaps after ties, and DENSERANK does not leave gaps. The desired meaning of “top N” determines which one is correct.
What are LAG, LEAD and running totals?
LAG and LEAD read values from earlier or later rows within an ordered window. A running total uses an aggregate window that expands through the current row. These calculations support trends, comparisons and cumulative reports while preserving detail records.
Difficulty is an editorial estimate. Completion means you chose to mark a question as understood.