Day 10: Advanced SQL Functions and Window Functions

 Day 10: Advanced SQL Functions and Window Functions

    1. Advanced SQL Functions

            These functions enhance data manipulation and analysis.

        1.1 String Functions:

• UPPER(), LOWER(): Change case.
• CONCAT(): Combine strings.
• SUBSTRING(): Extract part of a string.
• TRIM(): Remove leading/trailing spaces.

                Example:

                    SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

        1.2. Date Functions:

• NOW(): Current date and time.
• DATEADD(): Add intervals to a date.
• DATEDIFF(): Difference between dates.

                Example:

                    SELECT DATEDIFF(DAY, HireDate, GETDATE()) AS DaysWorked FROM Employees;

        1.3. Mathematical Functions:
                ROUND(), CEIL(), FLOOR(), ABS(): Perform numerical operations.                                                    Example:

                        SELECT ROUND(Salary, 2) AS RoundedSalary FROM Employees;

    2. Window Functions

            Window functions perform calculations across a set of rows related to the current row, without collapsing rows like aggregate functions.

• ROW_NUMBER(): Assigns a unique number to each row in a result set.
• Example: 
• SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum FROM Employees;
• RANK(): Assigns a rank to rows, with gaps for ties. 
• Example:
• SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;
• DENSE_RANK(): Similar to RANK() but without gaps. 
• Example:
• SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;
• NTILE(): Divides rows into a specified number of groups. 
• Example: 
• SELECT Name, Salary, NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile FROM Employees;
  
• LEAD() and LAG(): Access data from the next or previous row.
  
• Example:
• SELECT Name, Salary, LEAD(Salary) OVER (ORDER BY Salary) AS NextSalary FROM Employees;

•  Aggregate with PARTITION BY: Use PARTITION BY to calculate aggregates within subsets of data.
• Example:
• SELECT DepartmentID, Name, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID) AS DepartmentTotal FROM Employees;

Action Steps

1. Practice string, date, and math functions on your dataset.
2. Implement ROW_NUMBER(), RANK(), and PARTITION BY to analyze data.
3. Use LEAD() and LAG() to compare current rows with previous/next rows.