Advanced Query Techniques

Welcome to Day 3 of Database Decoded Week 3! After mastering normalization and security, it’s time to level up your SQL skills with advanced query techniques that simplify complex logic, supercharge reporting, and transform raw data into actionable insights. Today, we’ll dive into CTEs, window functions, and pivots—tools that separate SQL novices from pros. Let’s get started!

Why These Techniques Matter

Modern databases handle millions of rows, and writing efficient, readable queries is critical. For example:

• CTEs break down complex logic into manageable steps.

• Window Functions calculate rankings, running totals, or moving averages without collapsing rows.

• Pivot Tables reshape data for reports (e.g., monthly sales by region).

Let’s explore each with real-world examples using the Employees and Projects tables.

Topics Covered

1. Common Table Expressions (CTEs): Simplify Complex Queries

A CTE is a temporary result set you can reference within a larger query. Think of it as a named subquery that improves readability.

Real-World Example: Calculate the average salary by department and compare it to individual salaries.

WITH DepartmentAvg AS (  
    SELECT  
        DepartmentID,  
        AVG(Salary) AS AvgSalary  
    FROM Employees  
    GROUP BY DepartmentID  
)  
SELECT  
    E.EmployeeID,  
    E.FirstName,  
    E.Salary,  
    D.AvgSalary,  
    E.Salary - D.AvgSalary AS DifferenceFromAvg  
FROM Employees E  
INNER JOIN DepartmentAvg D  
    ON E.DepartmentID = D.DepartmentID;  

Result:

Why Use CTEs:

• Break complex logic into steps.

• Reuse the same subquery multiple times.

2. Window Functions: Rankings, Totals, and Trends

Window functions perform calculations across a set of rows related to the current row without grouping.

Example 1: Row Number and Rankings

Rank employees by salary within their department:

SELECT  
    EmployeeID,  
    FirstName,  
    DepartmentID,  
    Salary,  
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank,  
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DepartmentRank  
FROM Employees;  

Result:

Example 2: Running Total

Calculate cumulative project costs over time:

SELECT  
    ProjectID,  
    ProjectName,  
    Cost,  
    SUM(Cost) OVER (ORDER BY StartDate) AS RunningTotal  
FROM Projects;  

Why Use Window Functions:

• Calculate metrics without collapsing rows.

• Simplify trend analysis (e.g., month-over-month growth).

3. Pivot Tables: Transform Rows to Columns

Pivoting converts row data into a columnar format, ideal for reports.

Real-World Example: Summarize project counts by status (e.g., Active, Completed, On Hold).

SELECT *  
FROM (  
    SELECT Status, ProjectID  
    FROM Projects  
) AS SourceTable  
PIVOT (  
    COUNT(ProjectID)  
    FOR Status IN ([Active], [Completed], [On Hold])  
) AS PivotTable;  

Result:

Why Use Pivots:

• Simplify dashboards and summaries.

• Make data more readable for stakeholders.

Practice Tasks

Task 1: CTE Challenge

Using the Employees, Projects, and Departments tables:

1. Write a CTE to calculate the total number of projects per department.

2. Join the result with Departments to show department names.

Hint:

WITH DeptProjects AS (  
    SELECT DepartmentID, COUNT(ProjectID) AS TotalProjects  
    FROM Employees E  
    INNER JOIN Projects P ON E.EmployeeID = P.EmployeeID  
    GROUP BY DepartmentID  
)  
SELECT D.DepartmentName, DP.TotalProjects  
FROM DeptProjects DP  
INNER JOIN Departments D ON DP.DepartmentID = D.DepartmentID;  

Task 2: Window Function Drill

1. Rank employees by salary across the entire company using RANK().

2. Calculate a 3-month moving average of project costs.

Task 3: Pivot in Action

1. Pivot the Employees table to show the count of employees by department and salary range (e.g., <50k, 50k-70k, >70k).

Key Takeaways

• CTEs: Organize complex logic into reusable blocks.

• Window Functions: Compute rankings, running totals, and trends without collapsing rows.

• Pivot Tables: Reshape data for intuitive reporting.

• Balance: These tools are powerful but can impact performance—always test with Execution Plan.

What’s Next?

Day 4: Backup and Recovery Strategies

• Full Backups: Safeguard your entire database.

• Point-in-Time Recovery: Restore data to a specific moment.

• Disaster Planning: Prepare for the worst-case scenario.

Pro Tip: Practice today’s tasks by analyzing query execution plans. Compare the performance of CTEs vs. subqueries!

Challenge: Can you write a window function to calculate a running total of salaries within each department? Share your solution below! 

Loved these techniques? Let us know how you’ll use them in your next project!