Window Functions ① — OVER and PARTITION BY

Data used in this article — sales and employee

A window function computes things like per-group totals, ranks, and running totals row by row, and adds a single column to your result.

While GROUP BY collapses many rows into one summary row, a window function leaves every detail row in place and just adds the computed value alongside.

Before jumping into the exercises, take a look at the column definitions and sample data of the two tables we'll use — sales and employee.

① Run PRAGMA table_info(sales); and PRAGMA table_info(employee); to check both tables' column definitions.

② Run SELECT * FROM sales LIMIT 5; and SELECT * FROM employee LIMIT 5; to preview the first 5 rows of each. Notice that the dept_id column in employee is NULL for some employees.

OVER () — compute over every row and add a column

Let's start with the simplest shape: aggregate(column) OVER ().

With empty parentheses, the window covers the entire result set (every row that was selected).

Adding SUM(amount) OVER () as a 4th column gives you a new grand_total column where every row has the same value (the overall total).

The number of rows doesn't change — you just get one more column.

-- OVER () with empty parens covers the entire result set
-- All 5 detail rows stay, and grand_total carries the same value on every row
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER ()   AS grand_total,
  COUNT(*)    OVER ()   AS row_count,
  AVG(amount) OVER ()   AS grand_avg
FROM sales;

Imagine you want a list of individual sales, with the overall total and each sale's share of that total shown alongside. (If your query runs correctly, an explanation will appear.)

① Select sale_id, emp_id, and amount from sales.

② Add SUM(amount) OVER () as a 4th column aliased grand_total. Every row will carry the same overall total.

③ Add *amount 100.0 / SUM(amount) OVER ()** as a 5th column aliased pct_of_total — each row's share of the overall total.

④ The grader checks row order, so end with ORDER BY sale_id.

Comparing with GROUP BY — collapse or keep the rows

Putting GROUP BY and a window function side by side makes the difference obvious.

With GROUP BY emp_id + SUM(amount), you collapse each employee's rows into a single row that returns just the total.

With SUM(amount) OVER (), every detail row stays put, and you just get one more column with the aggregate value.

Pick one based on what you want: just the totals, or both the totals and the detail in one table.

-- GROUP BY: collapses to 1 row per employee — detail is gone
SELECT emp_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM sales
WHERE emp_id = 2
GROUP BY emp_id;
-- result: 1 row (emp_id=2, cnt=5, total=2150000)

-- Window function: keeps all 5 detail rows, with the overall total alongside
SELECT sale_id, emp_id, amount,
  SUM(amount) OVER () AS grand_total
FROM sales
WHERE emp_id = 2;
-- result: 5 rows, grand_total = 2150000 on every row

Write the same "give me the total" computation two ways — once with GROUP BY and once with OVER () — and compare the results side by side.

① Start with the GROUP BY version: filter sales to emp_id = 2, then select emp_id, COUNT(*) AS cnt, and SUM(amount) AS total, and aggregate with GROUP BY emp_id. Watch how it collapses to 1 row.

② Then write the window-function version: keep emp_id = 2's detail rows and add SUM(amount) OVER () AS grand_total. Select sale_id, emp_id, amount, grand_total. You should get 5 rows, each with the same total alongside.

③ Run both queries in the same console and compare "1 row" vs. "5 rows" with your own eyes. The grader looks at the window-function output, so end ② with ORDER BY sale_id to fix the order.

PARTITION BY — split the window into groups

Where OVER () treats the whole result as a single window (the range of rows to aggregate over), OVER (PARTITION BY column) splits the window by the values in that column.

With PARTITION BY emp_id, the window is split per employee, and each row gets its own employee's total alongside.

In the diagram below, all 4 rows for emp_id=1 get the same "emp_id=1 total = 970,000" added, and all 5 rows for emp_id=2 get "emp_id=2 total = 2,150,000".

-- Add per-employee total, average, and count to every detail row
SELECT sale_id, emp_id, amount,
  SUM(amount)   OVER (PARTITION BY emp_id) AS emp_total,
  AVG(amount)   OVER (PARTITION BY emp_id) AS emp_avg,
  COUNT(*)      OVER (PARTITION BY emp_id) AS emp_count
FROM sales
ORDER BY emp_id, sale_id;

-- Percent-of-employee-total: what share is this sale of that employee's total?
SELECT sale_id, emp_id, amount,
  amount * 100.0 / SUM(amount) OVER (PARTITION BY emp_id) AS pct_of_emp
FROM sales
ORDER BY emp_id, sale_id;

Imagine you want to show each sale alongside that employee's total, average, and count of sales.

① Select sale_id, emp_id, and amount from sales.

② Using a window partitioned by employee, add three columns aliased emp_total (sum), emp_avg (average), and emp_count (count).

③ The grader checks row order, so end with ORDER BY emp_id, sale_id (ascending by emp_id, then by sale_id within each employee).

Combine with JOIN to get per-department aggregates

Window functions work on JOIN results too.

JOIN sales with employee to pull in dept_id (department ID), then PARTITION BY e.dept_id lets you attach the total sales for that employee's department to every sale row.

The PARTITION BY column can be one you pulled in from another table via JOIN.

Window functions are evaluated after FROM / JOIN / WHERE, once the row set is settled — so the flow is "JOIN to assemble the rows you need → distribute window aggregates across that row set".

-- JOIN sales with employee and add per-department total and count to every detail
SELECT s.sale_id, e.name, e.dept_id, s.amount,
  SUM(s.amount) OVER (PARTITION BY e.dept_id) AS dept_total,
  COUNT(*)      OVER (PARTITION BY e.dept_id) AS dept_sales
FROM sales AS s
JOIN employee AS e ON s.emp_id = e.emp_id
ORDER BY e.dept_id, s.sale_id;

Imagine you want a report listing each sale with the employee's name and department, plus the total sales for that department alongside.

① INNER JOIN sales (aliased s) with employee (aliased e) on s.emp_id = e.emp_id.

② Select s.sale_id, e.name, e.dept_id, and s.amount.

③ With a window partitioned by department (PARTITION BY e.dept_id), add dept_total as the 5th column showing the department's total sales.

④ The grader checks row order, so end with ORDER BY e.dept_id, s.sale_id (ascending by dept_id, then by sale_id within each department).