Window functions: keeping every row

GROUP BY folds rows; OVER does not

GROUP BY gives one row per group — you get the total and lose the detail. A window function computes across a set of rows and still returns every row:

SELECT
  name,
  department_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_average
FROM employees;

Every employee, with their department's average beside them. Getting that with GROUP BY needs a subquery and a join; here it is one clause, and the comparison the question was really about — this salary against that average — is now a single expression away.

PARTITION BY and ORDER BY do different jobs

OVER ()                                    -- the whole result set is one window
OVER (PARTITION BY department_id)          -- one window per department
OVER (ORDER BY hired_at)                   -- ordered, and cumulative by default
OVER (PARTITION BY department_id ORDER BY salary DESC)

PARTITION BY decides which rows are in the window — it is GROUP BY that does not collapse. ORDER BY decides the order inside the window, which matters for two reasons: ranking needs it, and adding it silently changes an aggregate into a running one.

The three ranking functions differ on ties

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

ROW_NUMBER never ties and is arbitrary between equal rows — add a tiebreaker to ORDER BY if the result must be stable. RANK leaves gaps after a tie, DENSE_RANK does not. "Top 3 salaries" is a different question depending on which you pick.

Top N per group

The canonical use, and the reason window functions are asked about in interviews:

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

The subquery is not optional: a window function is computed after WHERE, so it cannot be filtered in the same SELECT that produced it. Nest it, or use QUALIFY on the engines that have it.

The frame is what makes a total run

Adding ORDER BY to an aggregate window brings a default frame — everything from the start of the partition up to the current row:

SELECT day, revenue,
  SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily;

That default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and it is why the same SUM means "total" without ORDER BY and "running total" with it. State a frame when you want a moving window instead:

SELECT day, revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily;

ROWS counts rows; RANGE counts values, so duplicate days are treated as one step. On a series with ties, they give different answers.

Reaching the neighbouring row

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly;

LAG and LEAD remove the self-join that month-over-month comparisons used to need. The first row's LAG is NULL — that is not a bug to paper over, it is the honest statement that there is no previous month.