Why should you care?

Leaderboards, “top N per branch”, and running totals in banking dashboards use window functions heavily.

See it live — copy this example

Run these statements in SQL Developer or SQL*Plus against your lab PDB. Prefer a practice user — not SYS — for application SQL.

SELECT emp_name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept,
       SUM(salary) OVER (PARTITION BY dept_id) AS dept_salary_total
FROM employees
ORDER BY dept_id, rank_in_dept;

What happened?

• PARTITION BY dept_id restarts numbering per department.
• ORDER BY salary DESC ranks highest salary as 1.
• SUM() OVER adds salaries in the same dept while still showing each employee row.

Practice next

1. Ensure employees table has at least 4 rows in 2 departments.
2. Run the window query.
3. Filter outer query with a subquery or inline view where rank_in_dept <= 2.
4. Add AVG(salary) OVER (PARTITION BY dept_id).
5. Order by salary ASC and compare ranks.

Remember

Window functions keep row detail. PARTITION BY = groups; ORDER BY = ranking order. Great for top-N and running totals.

Top earners per branch

Retail banking wants top 2 salaries per department for a bonus list.

Outcome: ROW_NUMBER filter delivers the list without Excel.