Week 2, Day 3: Advanced SQL for Analytics

Welcome to Day 3 of Week 2: Advanced Analytics and Visualization! Today, we’re diving into Advanced SQL—a must-have skill for any data professional. Whether you’re analyzing large datasets, building reports, or preparing data for machine learning, mastering advanced SQL techniques will make your work faster, more efficient, and more powerful. Let’s explore window functions, CTEs, and other game-changing SQL features!

Why Advanced SQL Matters

SQL is the backbone of data analysis. Advanced techniques like window functions and CTEs allow you to:

• Simplify Complex Queries: Break down problems into manageable steps.

• Improve Performance: Write efficient queries that run faster.

• Unlock New Insights: Perform advanced calculations and transformations.

Topics Covered

1. Common Table Expressions (CTEs)

CTEs are temporary result sets that you can reference within a SQL query. They make complex queries easier to read and maintain.

Example: Calculate the average salary by department.

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

Why Use CTEs?

• Improve query readability.

• Break complex logic into smaller, reusable parts.

2. Window Functions

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

Common Window Functions

• ROW_NUMBER(): Assigns a unique number to each row.

• RANK(): Assigns a rank to each row, with gaps for ties.

• SUM(): Calculates a running total.

Example: Rank employees by salary within their department.

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

Why Use Window Functions?

• Perform calculations without grouping data.

• Analyze trends and rankings within subsets of data.

3. Advanced Joins

Joins are fundamental to SQL, but advanced techniques can unlock even more power.

Self-Join

Join a table to itself to compare rows within the same table.

Example: Find employees who earn more than their manager.

SELECT  
    E1.EmployeeID,  
    E1.FirstName,  
    E1.Salary,  
    E2.FirstName AS ManagerName,  
    E2.Salary AS ManagerSalary  
FROM Employees E1  
JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID  
WHERE E1.Salary > E2.Salary;  

Cross Join

Combine every row from one table with every row from another.

Example: Generate all possible combinations of products and regions.

SELECT  
    P.ProductName,  
    R.RegionName  
FROM Products P  
CROSS JOIN Regions R;  

4. Subqueries

Subqueries allow you to nest queries within queries, making it easier to solve complex problems.

Example: Find employees who earn more than the average salary.

SELECT  
    EmployeeID,  
    FirstName,  
    Salary  
FROM Employees  
WHERE Salary > (SELECT AVG(Salary) FROM Employees);  

Why Use Subqueries?

• Break down complex logic into smaller steps.

• Use the result of one query as input for another.

Pro Tip: Optimize Your Queries

• Indexes: Create indexes on frequently queried columns.

• EXPLAIN: Use the EXPLAIN command to analyze query performance.

• Limit Data: Use LIMIT or TOP to test queries on small datasets.

Practice Tasks

Task 1: Use CTEs to Analyze Data

1. Write a CTE to calculate total sales by region.

2. Join the CTE with another table to analyze sales performance.

Task 2: Apply Window Functions

1. Rank customers by total purchase amount.

2. Calculate a running total of sales over time.

Task 3: Solve a Problem with Subqueries

1. Find products that have never been ordered.

2. Identify employees who manage other employees.

Key Takeaways

• CTEs: Simplify complex queries and improve readability.

• Window Functions: Perform advanced calculations without grouping data.

• Advanced Joins: Compare rows within the same table or generate combinations.

• Subqueries: Nest queries to solve complex problems.

What’s Next?

Day 4: Data Storytelling

• Learn how to present data insights effectively.

• Explore tools like PowerPoint, Canva, and Flourish.

Pro Tip: Practice advanced SQL techniques on real-world datasets to build your skills.

Challenge: Can you write a SQL query that calculates the percentage of total sales for each product category? Share your solution below!

Enjoyed this guide? Let us know how you’re using advanced SQL in your projects!