SQL Window Functions — Basics of OVER, PARTITION BY, Totals

QUESTION 1

OVER() Fundamentals — Attach an Overall Aggregate to Every Row Without Collapsing Rows

OVERSUMShare of TotalFundamentals

Background ▼

Like GROUP BY, window functions aggregate grouped data, but they have one exceptionally powerful property: they return aggregate results while preserving every original row instead of collapsing rows.

SELECT
  key_col,
  num_col,
  SUM(num_col) OVER() AS total_all  -- OVER() marks this as a window function
FROM table_name;

What OVER() does: Adding OVER() after a function such as SUM or AVG treats the entire result set as one window and attaches the same aggregate value to every row.

Problem ▼

Using the sales table below, which contains individual sales records, return each representative's sales amount, the overall sales total, and the row's share of that total.

Calculate the share as amount / overall total, round it to four decimal places, and sort the results by amount from highest to lowest.

Input table

▸ sales

emp_id	emp_name	amount
E1	Tanaka	150000
E2	Sato	200000
E3	Suzuki	120000
E4	Takahashi	180000

Expected Output ▼

emp_name	amount	total_amount	ratio
Sato	200000	650000	0.3077
Takahashi	180000	650000	0.2769
Tanaka	150000	650000	0.2308
Suzuki	120000	650000	0.1846

Model Answer ▼

SELECT
  emp_name,
  amount,
  SUM(amount) OVER() AS total_amount,   -- Attach the table-wide amount total (650,000) to every row
  ROUND(amount * 1.0 / SUM(amount) OVER(), 4) AS ratio  -- Round the share of total to four decimal places
FROM sales
ORDER BY amount DESC;

/*
  Logical evaluation order:
  1. FROM sales               → Read 4 rows
  2. WHERE/GROUP BY (if any)  → Filter or aggregate rows
  3. Window function          → Calculate the total and attach it to each row
  4. SELECT                   → Calculate and return the share
  5. ORDER BY                 → Sort by sales descending
  */

Explanation (table transitions & key points) ▼

SELECT
  emp_name,
  amount,
  SUM(amount) OVER() AS total_amount,
  ROUND(amount * 1.0 / SUM(amount) OVER(), 4) AS ratio
FROM sales
ORDER BY amount DESC;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM salesRead all 4 rows from the sales table.

1 / 3

emp_name	amount
Tanaka	150000
Sato	200000
Suzuki	120000
Takahashi	180000

4 rows read

LEARNING POINTS

How this differs from GROUP BY: GROUP BY collapses rows to one row per group. To return individual sales alongside the overall total, you would otherwise need to aggregate in a subquery and join the result back. OVER() produces both in one query.

Evaluation order: Window functions are evaluated after all filtering and aggregation by WHERE and GROUP BY, immediately before the final SELECT projection. Therefore, with WHERE amount > 10000, the total includes only rows above 10,000.

▤ GROUP BY vs OVER() — Two Ways to Query the Same sales Table

✗ GROUP BY (rows collapse)

SUM(amount)
650,000

4 rows → aggregated into 1 row
Individual sales details disappear

→

✓ OVER() (rows preserved)

emp_name	amount	SUM(amount) OVER()
Tanaka	150,000	650,000
Sato	200,000	650,000
Suzuki	120,000	650,000
Takahashi	180,000	650,000

The overall total is attached while all 4 rows remain
Each row can calculate its share directly

ANTI-PATTERNS

Using a window function in WHERE: WHERE SUM(amount) OVER() > 500000 causes an error because WHERE is evaluated before window functions. To filter by a window-function result, calculate it in a subquery (or CTE), then filter in the outer query.

QUESTION 2

PARTITION BY — Divide a Window by Department or Category

PARTITION BYPer-GroupDepartment Total

Background ▼

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
  group_col, key_col, num_col,
  SUM(num_col) OVER(PARTITION BY group_col) AS group_total
FROM table_name;

Role of PARTITION BY: It divides the full data set into smaller partitions, such as one per department, and aggregates within each partition. The result is attached to each original row.

Problem ▼

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

Input table

▸ employees

emp_name	dept	salary
Tanaka	Sales	300000
Sato	Sales	280000
Suzuki	Engineering	400000
Takahashi	Engineering	350000
Ito	HR	320000

Expected Output ▼

emp_name	dept	salary	dept_total
Tanaka	Sales	300000	580000
Sato	Sales	280000	580000
Suzuki	Engineering	400000	750000
Takahashi	Engineering	350000	750000
Ito	HR	320000	320000

Model Answer ▼

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 DESC, emp_name;

/*
  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 CASE dept WHEN 'Sales' THEN 1 WHEN 'Engineering' THEN 2 WHEN 'HR' THEN 3 END, salary DESC, emp_name → Sort departments as Sales, Engineering, and HR, then by salary descending and emp_name ascending for ties
*/

Explanation (table transitions & key points) ▼

SELECT
  emp_name, dept, salary,
  SUM(salary) OVER(PARTITION BY dept) AS dept_total
FROM employees
ORDER BY
  CASE dept
    WHEN 'Sales' THEN 1
    WHEN 'Engineering' THEN 2
    WHEN 'HR' THEN 3
  END, salary DESC, emp_name;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM employeesRead all 5 rows from the employees table.

1 / 3

emp_name	dept	salary
Tanaka	Sales	300000
Sato	Sales	280000
Suzuki	Engineering	400000
Takahashi	Engineering	350000
Ito	HR	320000

5 rows read

LEARNING POINTS

PARTITION BY is the window-function counterpart to GROUP BY: Both define aggregation groups, but PARTITION BY leaves detail rows intact. This makes relative analysis—such as comparing an employee's salary with the department average—remarkably concise.

Multiple columns are allowed: With PARTITION BY year, month, you can attach the total for each year-month to every row.

▤ Effect of PARTITION BY dept — Data Divided into Three Partitions

dept (partition key)	emp_name	salary	dept_total (SUM within partition)
Sales	Tanaka	300,000	580,000
Sales	Sato	280,000	580,000
Engineering	Suzuki	400,000	750,000
Engineering	Takahashi	350,000	750,000
HR	Ito	320,000	320,000

Dashed lines mark partition boundaries. When the department changes, the aggregation window resets and the total is recalculated only within the new partition.
The row count remains 5.

ANTI-PATTERNS

Confusing GROUP BY with window functions: Writing SELECT dept, SUM(salary) OVER(PARTITION BY dept) FROM employees GROUP BY dept; is incorrect. GROUP BY collapses rows, so combining it this way with OVER(PARTITION BY) produces unintended results or an error. For a simple group total, use ordinary SUM() ... GROUP BY.

QUESTION 3

OVER(ORDER BY) — Calculate a Running Total in Date Order

ORDER BYRunning TotalTrend

Background ▼

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
  sort_col, num_col,
  SUM(num_col) OVER(ORDER BY sort_col ASC) AS running_total
FROM table_name;

Implicit frame: With ORDER BY inside OVER, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: from the start through all rows whose sort value equals the current row's value. This produces a running total, and rows sharing the same date are included in the same frame.

Problem ▼

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

Input table

▸ daily_sales

sale_date	amount
04-01	10000
04-02	15000
04-03	12000
04-04	20000

Expected Output ▼

sale_date	amount	running_total
04-01	10000	10000
04-02	15000	25000
04-03	12000	37000
04-04	20000	57000

Model Answer ▼

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
  */

Explanation (table transitions & key points) ▼

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

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM daily_salesRead the table.

1 / 3

sale_date	amount
04-01	10000
04-02	15000
04-03	12000
04-04	20000

4 rows read

LEARNING POINTS

Running totals by department with PARTITION BY: SUM(amount) OVER(PARTITION BY dept ORDER BY date) calculates a separate running sales total for each department. This combination is essential in production dashboard queries.

Watch how peers are handled: When multiple rows have the same ORDER BY value, such as the same date, the default RANGE frame processes them as one peer group, so they receive the same running total. For a row-by-row result, add a unique key as in ORDER BY date, id.

▤ Running Total with ORDER BY — The Window Frame Expands by Row Here

sale_date	amount	Frame (start through current row)	running_total
04-01	10,000	10,000	10,000
04-02	15,000	10,000 + 15,000	25,000
04-03	12,000	10,000 + 15,000 + 12,000	37,000
04-04	20,000	10,000 + 15,000 + 12,000 + 20,000	57,000

Bold blue = current row (CURRENT ROW)　Pale blue = earlier rows (UNBOUNDED PRECEDING)
Because these dates are unique, the frame expands by one row at a time; summing every row in it produces the running total.

ANTI-PATTERNS

Ordering only by a non-unique column: If several daily rows exist and you use only ORDER BY month, the default RANGE frame adds every row in the same month together, rather than producing the intended row-by-row running total. Add a unique sort key when row-level progression is required.

QUESTION 4

ROW_NUMBER() — Assign Sequential Numbers Within Each Group

ROW_NUMBERPARTITION BYFoundation for Deduplication

Background ▼

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
  group_col, ts_col,
  ROW_NUMBER() OVER(
    PARTITION BY group_col     -- Reset for each group and restart at 1
    ORDER BY ts_col DESC       -- Number from newest to oldest
  ) AS rn
FROM table_name;

Essential production technique: To retrieve only the newest access record for each user, the standard SQL pattern is to keep only rows where rn=1 from the query above. First, master how the numbering works.

Problem ▼

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.

Input table

▸ logins

user_id	login_time
U1	2024-04-01 10:00
U1	2024-04-02 12:00
U1	2024-04-03 09:00
U2	2024-04-01 11:00
U2	2024-04-04 15:00

Expected Output ▼

user_id	login_time	rn
U1	2024-04-03 09:00	1
U1	2024-04-02 12:00	2
U1	2024-04-01 10:00	3
U2	2024-04-04 15:00	1
U2	2024-04-01 11:00	2

Model Answer ▼

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
  */

Explanation (table transitions & key points) ▼

SELECT
  user_id, login_time,
  ROW_NUMBER() OVER(
    PARTITION BY user_id
    ORDER BY     login_time DESC
  ) AS rn
FROM logins
ORDER BY user_id, rn;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM loginsRead the table.

1 / 3

user_id	login_time
U1	04-01 10:00
U1	04-02 12:00
U1	04-03 09:00
U2	04-01 11:00
U2	04-04 15:00

5 rows read

LEARNING POINTS

Why ROW_NUMBER: MAX(login_time) GROUP BY user_id can find the latest time per group, but it cannot simultaneously return other columns, such as the device or IP address used for the login. Assigning ROW_NUMBER and later filtering with WHERE rn = 1 retrieves the entire latest row and solves this problem.

▤ GROUP BY MAX() vs ROW_NUMBER() — Retrieving Every Column from the Latest Row

✗ GROUP BY user_id — Other columns unavailable

SELECT user_id, MAX(login_time)
FROM logins
GROUP BY user_id;

user_id	MAX(login_time)
U1	04-03 09:00
U2	04-04 15:00

The device type and other columns are unavailable.

✓ ROW_NUMBER + WHERE rn=1 — Every column available

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER(
      PARTITION BY user_id
      ORDER BY login_time DESC
    ) AS rn
  FROM logins
) t WHERE rn = 1;

user_id	login_time	rn
U1	04-03 09:00	1
U2	04-04 15:00	1

✓ Retrieve every column from each latest row

ANTI-PATTERNS

Omitting PARTITION BY or ORDER BY: Writing only ROW_NUMBER() OVER() numbers the entire table in an unspecified order. When the requirement says “within each group” and “in this order,” specify both PARTITION BY and ORDER BY.

QUESTION 5

RANK / DENSE_RANK — Ranking and Handling Ties

RANKDENSE_RANKRanking

Background ▼

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)

SELECT id_col,
       ROW_NUMBER() OVER (ORDER BY num_col DESC) AS rn,        -- Plain sequence
       RANK()       OVER (ORDER BY num_col DESC) AS rnk,       -- Ties share a rank, next rank skips
       DENSE_RANK() OVER (ORDER BY num_col DESC) AS dense_rnk  -- Ties share a rank, next rank follows
FROM   table_name;

Only tie handling differs: Given the same ORDER BY, the three functions differ only in how they number tied rows. ROW_NUMBER() always assigns distinct numbers, so which tied row comes first is undefined unless the ordering breaks the tie. Add a unique column at the end of ORDER BY to make it deterministic.

Problem ▼

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.

Input table

▸ scores

student	score
Student A	95
Student B	95
Student C	88
Student D	88
Student E	70

Expected Output ▼

student	score	rnk	dense_rnk	row_num
Student A	95	1	1	1
Student B	95	1	1	2
Student C	88	3	2	3
Student D	88	3	2	4
Student E	70	5	3	5

Model Answer ▼

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
  */

Explanation (table transitions & key points) ▼

SELECT
  student, score,
  RANK()       OVER(ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rnk,
  ROW_NUMBER() OVER(ORDER BY score DESC, student) AS row_num
FROM scores
ORDER BY score DESC, student;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM scoresRead the table.

1 / 3

student	score
Student A	95
Student B	95
Student C	88
Student D	88
Student E	70

5 rows read

LEARNING POINTS

Choosing in production: For a top-three list, RANK can yield ranks 1, 1, and 3 and return three people when there is a tie. DENSE_RANK can yield 1, 1, 2, and 3 and may return four or more people. ROW_NUMBER always returns exactly three rows. Choose according to the required treatment of ties.

▤ Comparing Three Functions — What Happens with Ties (Two 95s and Two 88s)

student	score	RANK() Leaves gaps after ties	DENSE_RANK() No gaps after ties	ROW_NUMBER() Always unique and sequential
Student A	95	1	1	1
Student B	95	1 ← tied	1 ← tied	2 ← tie broken
Student C	88	3 ← rank 2 skipped	2 ← no gap	3
Student D	88	3 ← tied	2 ← tied	4 ← tie broken
Student E	70	5 ← rank 4 skipped	3	5

RANK: Two tied at first means rank 2 is skipped → next is rank 3 DENSE_RANK: No gaps; ranks are always consecutive integers ROW_NUMBER: Tied rows still receive different numbers