Window functions are a powerful feature in SQL that allow you to perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a group of rows, window functions return a value for each row in the result set.

1. Window Function Syntax:

   function_name (expression) OVER (
       [PARTITION BY partition_expression]
       [ORDER BY sort_expression]
       [frame_clause]
   )
   

2. Components:

   • function_name: The name of the window function (e.g., ROW_NUMBER(), RANK(), SUM(), etc.).
   • expression: The column or expression to be used in the function.
   • PARTITION BY: Divides the result set into partitions to which the window function is applied.
   • ORDER BY: Defines the order of rows within each partition.
   • frame_clause: Specifies the subset of rows within the partition to be considered by the window function.

1. ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.

   SELECT 
       column1,
       ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) AS row_num
   FROM 
       table_name;
   

2. RANK(): Assigns a rank to each row within a partition, with gaps in the ranking for ties.

   SELECT 
       column1,
       RANK() OVER (PARTITION BY column2 ORDER BY column3) AS rank
   FROM 
       table_name;
   

3. DENSE_RANK(): Similar to RANK(), but without gaps in the ranking for ties.

   SELECT 
       column1,
       DENSE_RANK() OVER (PARTITION BY column2 ORDER BY column3) AS dense_rank
   FROM 
       table_name;
   

4. SUM(): Calculates the cumulative sum of a column's values within a partition.

   SELECT 
       column1,
       SUM(column4) OVER (PARTITION BY column2 ORDER BY column3) AS cumulative_sum
   FROM 
       table_name;
   

5. AVG(): Calculates the cumulative average of a column's values within a partition.

   SELECT 
       column1,
       AVG(column4) OVER (PARTITION BY column2 ORDER BY column3) AS cumulative_avg
   FROM 
       table_name;
   

Explanation: This query assigns a unique row number to each employee within their department, ordered by salary in descending order.

Explanation: This query ranks employees within their department based on their salary. Employees with the same salary receive the same rank, and the next rank is skipped.

Explanation: This query calculates the cumulative salary for each employee within their department, ordered by employee ID.

Task: Write a query to assign row numbers to each product within its category, ordered by price in ascending order.

Solution:

Task: Write a query to rank employees within their department based on their performance score. Handle ties by assigning the same rank to employees with the same score.

Solution:

Task: Write a query to calculate the cumulative sales for each salesperson within their region, ordered by the date of sale.

Solution:

• Incorrect Partitioning: Ensure that the PARTITION BY clause correctly defines the logical groups for your calculation.
• Ordering Issues: The ORDER BY clause within the OVER clause is crucial for functions like ROW_NUMBER() and RANK(). Ensure it reflects the desired order.
• Frame Clause Misuse: Be cautious with the frame clause. The default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which might not always be what you need.

Window functions are a versatile tool in SQL that allow for complex calculations over partitions of data. By understanding and utilizing window functions, you can perform advanced data analysis and reporting tasks with ease. In the next module, we will delve into database design principles, which will help you structure your databases efficiently and effectively.