window

Window Functions in a Filtered Query

Compute each employee's salary share of their department's total payroll.

Overview

Window functions can sit alongside a WHERE clause: the WHERE narrows the rows first, then the window computes over what remains. Here we show, for Engineering and Finance only, each employee's salary as a percentage of their department's total.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

WHERE d.name IN ('Engineering','Finance') — filters rows before the window sees them.
SUM(salary) OVER (PARTITION BY department_id) — per-department total, repeated on every row of that department.
100.0 * salary / dept_total — each person's share, as a percentage.

Order of operations

WHERE → GROUP BY → HAVING → window functions → ORDER BY. The window sees the post-filter rows.

Variations

Share of a product category's revenue per order:

Store (Customers/Orders/Products)

Loading database engine...

Common Mistakes

Expecting the window to see filtered-out rows. A WHERE exclusion is permanent — the window can't "reach back" to dropped rows.
Repeating the filter inside and outside. Don't also constrain the window with a FILTER that contradicts the WHERE; keep it simple.
Integer division. Use 100.0 * (or cast) to avoid truncating to 0.