Aggregate and Window functions in SQL -01

When learnig SQL at the beginig it may look like more complcated and confused , because when to use this window functions and whether do we need use GROUP BY satment for each of slected fileds .So here im going t explain it in details

1. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. When used with window functions, they can provide detailed insights into data partitions.

a. AVG()

• Purpose: Calculates the average of the values within a partition.
• Query: Computes the average sale amount for each employee.
• Outcome: Shows the average sale amount per employee across all their sales.

b. MAX()

• Purpose: Returns the maximum value within a partition.
• Query: Computes the maximum sale amount for each employee.
• Outcome: Shows the highest sale amount recorded for each employee.

c. MIN()

• Purpose: Returns the minimum value within a partition.
• Query: Computes the minimum sale amount for each employee.
• Outcome: Shows the lowest sale amount recorded for each employee.

d. SUM()

• Purpose: Calculates the sum of values within a partition.
• Query: Computes the total sales amount for each employee.
• Outcome: Shows the cumulative sales amount for each employee.

e. COUNT()

• Purpose: Counts the number of rows within a partition.
• Query: Counts the number of sales records for each employee.
• Outcome: Shows how many sales records each employee has.

2. Ranking Functions

Ranking functions assign a rank to each row within a partition based on specified criteria. They are useful for generating rank-based insights.

a. ROW_NUMBER()

• Purpose: Assigns a unique sequential integer to rows within a partition, starting at 1 for the first row.
• Query: Ranks sales records for each employee based on the sale date.
• Outcome: Provides a sequential number for each sale record per employee.

b. RANK()

• Purpose: Assigns a rank to each row within a partition, with the same rank for ties and gaps in ranking.
• Query: Ranks sales amounts for each employee, with the highest sale getting the highest rank.
• Outcome: Shows the rank of each sale amount, with ties receiving the same rank.

c. DENSE_RANK()

• Purpose: Similar to RANK(), but without gaps in the ranking sequence for ties.
• Query: Ranks sales amounts for each employee, with no gaps between ranks.
• Outcome: Provides a dense ranking of sales amounts, where tied values get the same rank.

d. PERCENT_RANK()

• Purpose: Calculates the relative rank of each row within a partition as a percentage.
• Query: Computes the percentile rank of each sale amount within each employee’s sales.
• Outcome: Shows the rank of each sale amount as a percentage of the total sales for each employee.

3. Analytic Functions

Analytic functions perform calculations across a set of rows related to the current row. Unlike aggregate functions, they do not collapse the result set.

a. LAG()

• Purpose: Provides access to a value from a previous row within the same partition.
• Query: Retrieves the amount of the previous sale for each employee.
• Outcome: Shows the sale amount from the previous row (sale) for each current row (sale).

b. LEAD()

• Purpose: Provides access to a value from a subsequent row within the same partition.
• Query: Retrieves the amount of the next sale for each employee.
• Outcome: Shows the sale amount from the next row (sale) for each current row (sale).

c. FIRST_VALUE()

• Purpose: Returns the first value in the partition or window frame.
• Query: Retrieves the first sale amount for each employee based on the sale date.
• Outcome: Shows the first sale amount recorded for each employee.

d. LAST_VALUE()

• Purpose: Returns the last value in the partition or window frame.
• Query: Retrieves the last sale amount for each employee based on the sale date.
• Outcome: Shows the last sale amount recorded for each employee.

e. NTILE()

• Purpose: Divides the result set into a specified number of ranked groups or “tiles.”
• Query: Divides the sales amounts into two groups (tiles) for each employee.
• Outcome: Shows the group (tile) number for each sale amount, with values divided into the specified number of groups.

f. NTH_VALUE()

• Purpose: Returns the value of the nth row in the partition or window frame.
• Query: Retrieves the amount of the second sale for each employee.
• Outcome: Shows the value of the nth row (second sale) for each employee, with NULL if there aren’t enough rows.

These explanations should help you understand how each window function operates and how they can be applied to analyze your data. Let me know if you have any more questions!