Writing PARTITION BY and a column name inside OVER() divides the window, or aggregation scope, by each value in that column. It resembles GROUP BY, but it still does not collapse rows.

SELECT
  dept, name, salary,
  SUM(salary) OVER(PARTITION BY dept) AS dept_total
FROM employees;

From the employees table below, return each employee's name, department, salary, and the total salary for that employee's department.

SELECT
  emp_name,
  dept,
  salary,
  SUM(salary) OVER(PARTITION BY dept) AS dept_total  -- Attach each department's salary total to every row in it
FROM employees
ORDER BY
  CASE dept
    WHEN 'Sales' THEN 1
    WHEN 'Engineering' THEN 2
    WHEN 'HR' THEN 3
  END, salary;

/*
  Logical evaluation order:
  1. FROM employees → Read 5 rows
  2. Window function → Partition by dept and calculate SUM within each partition
  3. SELECT → Attach the calculated dept_total to each output row
  4. ORDER BY → Sort departments as Sales, Engineering, and HR, then by salary ascending
*/

Specifying ORDER BY and a column inside OVER changes the calculation from an overall aggregate to a running aggregate over the range from the first row through the current row.

SELECT
  date, amount,
  SUM(amount) OVER(ORDER BY date ASC) AS running_total
FROM daily_sales;

From the daily_sales table below, return each date's sales amount and the cumulative sales through that date as running_total.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER(ORDER BY sale_date ASC) AS running_total  -- Running total from the start through the current date
FROM daily_sales
ORDER BY sale_date;

/*
  Logical evaluation order:
  1. FROM daily_sales
  2. Window function evaluates rows in sale_date order
  3. SELECT returns the result
  */

ROW_NUMBER() is a window function that assigns each row a unique sequential number—1, 2, 3, and so on—in the specified order. It takes no arguments.

SELECT
  user_id, login_time,
  ROW_NUMBER() OVER(
    PARTITION BY user_id         -- Reset for each user and restart at 1
    ORDER BY login_time DESC     -- Number from newest to oldest
  ) AS rn
FROM logins;

The logins table below contains user login history.
For each user_id, assign row numbers rn of 1, 2, 3, and so on in descending order from the newest login_time.

SELECT
  user_id,
  login_time,
  ROW_NUMBER() OVER(
    PARTITION BY user_id          -- Number each user_id independently
    ORDER BY     login_time DESC  -- Descending time order makes the newest row 1
  ) AS rn
FROM logins
ORDER BY user_id, rn;

/*
  Logical evaluation order:
  1. FROM logins
  2. Create the group for user_id 'U1'
  3. Create the group for user_id 'U2'
  4. SELECT returns the result
  */

Three functions assign rankings, and they differ in how the next number advances after a tie, such as equal scores.

• ROW_NUMBER(): always assigns 1, 2, 3, 4 in sequence, distinguishing even tied rows
• RANK(): gives tied rows the same rank and leaves a gap afterward (1, 1, 3, 4)
• DENSE_RANK(): gives tied rows the same rank without leaving a gap afterward (1, 1, 2, 3)

Using the student test scores in the scores table below, calculate RANK(), DENSE_RANK(), and ROW_NUMBER() from highest to lowest score and compare their results.

SELECT
  student,
  score,
  RANK()       OVER(ORDER BY score DESC) AS rnk,        -- Leave a gap after ties (competition ranking)
  DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rnk,  -- Do not leave a gap after ties
  ROW_NUMBER() OVER(ORDER BY score DESC, student) AS row_num  -- Break ties by student for deterministic numbering
FROM scores                                          -- Assign a unique sequential number even to tied rows
ORDER BY score DESC, student;

/*
  Logical evaluation order:
  1. FROM scores
  2. Window functions evaluate rows by score descending
  3. SELECT returns the result
  */