Let's go through a detailed explanation of Aggregate Functions vs. Window Functions with a concrete example using sample data.

Sample Data

Sample Data
employee_id name    department  salary
1   Alice   HR  5000
2   Bob HR  6000
3   Charlie IT  7000
4   David   IT  8000
5   Eve IT  9000
6   Frank   Sales   5500
7   Grace   Sales   6500

Aggregate Function Example

Aggregate functions return one value per group of rows.

Use Case: Find the average salary per department.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Result:

• The query returns one row per department, showing the average salary for each department.
• Note: After grouping by department, the aggregate function AVG(salary) returns one value per group (i.e., per department).

Window Function Example

Window functions allow you to calculate values across a "window" of rows, while still returning every row in the original dataset.

Use Case: Find each employee’s salary and the average salary for their department, but without collapsing the rows.

SELECT employee_id, name, department, salary, 
       AVG(salary) OVER (PARTITION BY department) AS avg_salary_per_dept
FROM employees;

Result:

• The OVER (PARTITION BY department) clause defines the window of rows for the function to operate on.
• Each employee's row is retained in the output, and the window function AVG(salary) is calculated for each department and displayed alongside the original row data.
• You can see the average salary for each department is repeated for each employee within that department.

Key Observations

Aggregate Function:

• The result is grouped by the department, and one value is returned per group (department).
• Rows are collapsed into groups, and you don’t see individual employee data.

Window Function:

• The result shows every individual row (no grouping), but also includes the calculation of average salary for each department without collapsing the rows.
• The OVER (PARTITION BY department) clause tells SQL to calculate the average salary for the department but to still return every row.

Conclusion

Aggregate Functions are used when you need to collapse rows into groups and return summary values for those groups & Window Functions allow you to perform calculations like averages, ranks, and cumulative sums over a "window" of rows, while still keeping all original row data.

Both are powerful, but they serve different purposes depending on whether you need to group the data or analyze each row in the context of a group.