One search. Connected ideas.
Find the lesson, understand the concept, and prepare your answer.
10 results shown · grouped by type
Lessons4
A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.
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.
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.
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.
Interview questions4
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.
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.
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.
A useful SQL exercise starts with a small dataset whose expected answer can be checked manually. Window functions then demonstrate ranking while keeping each employee's fields available. The example below returns all employees tied for the highest salary in each department.
Modules2
SQL & PostgreSQL learning module
SQL & PostgreSQL learning module