Part 91: Introduction to Window Functions: OVER, PARTITION BY & ORDER BY

Window Functions perform calculations across a set of table rows that are related to the current row, without collapsing the rows into a single output row (unlike GROUP BY).

1. Syntax

function_name() OVER (
  [PARTITION BY partition_column]
  [ORDER BY sort_column]
  [window_frame_clause]
)

2. Real-World Comparison: GROUP BY vs Window Function

-- With Window Function: Displays individual salary alongside the department average!
SELECT 
  first_name,
  department,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary
FROM employees;

Every employee row is preserved!

Key Takeaways

• Window functions compute analytical summaries while keeping individual row details intact.

In Part 92, we explore Window Functions vs GROUP BY: Deep Architectural Comparison!