Explain the difference between PARTITION BY and GROUP BY. When would you use each?

GROUP BY collapses rows into groups and returns one row per group with aggregate values. PARTITION BY creates logical groups but keeps all original rows, adding group calculations to each row. Use GROUP BY when you only need summary data (like total sales per region). Use PARTITION BY when you need both individual row details AND group calculations (like each employee's salary plus their department average). PARTITION BY is essential for rankings, running totals, and comparative analysis where you need to see both the detail and the group context.

Write a query to rank employees within each department by salary, and also show each employee's salary as a percentage of their department's total payroll.

SELECT

employee_name,

department,

salary,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,

ROUND(

100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2

) as pct_of_dept_payroll,

SUM(salary) OVER (PARTITION BY department) as dept_total_payroll

FROM employees

ORDER BY department, dept_rank;

This query demonstrates PARTITION BY for department-level calculations while maintaining individual employee details. The RANK function creates rankings within each department partition, and we calculate each employee's contribution to their department's total payroll.