Making Sense of SQL: From Joins to Window Functions

SQL is more than just selecting rows from a table. Real-world databases store related information across multiple tables, and real-world questions often require analysis beyond simple totals. This is where JOINs and Window Functions shine. Window function A window function performs a calculation across a set of table rows that are related to the current row. Window functions can be compared to aggregate functions but unlike aggregate functions window functions does not cause rows to be grouped into a single output row that is the rows maintain their original identities. Common window functions ROW_NUMBER() Assigns a unique number to each row within a partition. SELECT name, department_id, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY name ) AS row_num FROM employees; Explanation: Each department starts numbering employees from 1. RANK() and DENSE_RANK() Used to rank values, often with ties. SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;