Writing SQL Like a Pro: Advanced Queries Explained

WSDA News | December 12, 2024

SQL isn’t just a tool for beginners—its true power comes into play when you start mastering advanced techniques. These techniques allow you to manipulate and query data with precision, solving complex business problems efficiently. In this article, we’ll explore real-life-inspired strategies that take your SQL skills from beginner to expert.

1. Using Window Functions for Enhanced Insights

What Are Window Functions? Window functions perform calculations across a set of table rows related to the current row, enabling operations like rankings, running totals, and moving averages without grouping data.

Real-Life Use Case: Imagine a retail company analyzing customer spending trends. With window functions, you can compute the total revenue generated by each customer while also calculating their rank among all customers.

Example Query:

SELECT  
    customer_id,  
    SUM(sales) OVER (PARTITION BY customer_id) AS total_sales,  
    RANK() OVER (ORDER BY SUM(sales) DESC) AS customer_rank  
FROM  
    sales_data;          

This query ranks customers based on their total spending, providing actionable insights for targeted marketing campaigns.

2. CTEs (Common Table Expressions) for Simplifying Queries

What Are CTEs? Common Table Expressions are temporary result sets that make complex queries easier to read and manage. They’re especially useful for breaking down multi-step operations.

Real-Life Use Case: You’re tasked with analyzing a company’s employee data to identify departments with the highest average salaries. Instead of writing a sprawling query, you can use CTEs to simplify each step.

Example Query:

WITH department_avg_salary AS (  
    SELECT  
        department,  
        AVG(salary) AS avg_salary  
    FROM  
        employees  
    GROUP BY  
        department  
)  
SELECT *  
FROM department_avg_salary  
WHERE avg_salary > 100000;             

This approach breaks the task into manageable parts, improving query readability.

3. Subqueries for Dynamic Filtering

What Are Subqueries? Subqueries are queries nested inside other queries, used to filter, calculate, or generate temporary tables.

Real-Life Use Case: Consider a logistics company that wants to identify orders shipped by their top-performing couriers. Subqueries allow dynamic filtering based on multiple conditions.

Example Query:

SELECT  
    order_id, courier_id  
FROM  
    orders  
WHERE  
    courier_id IN (  
        SELECT  
            courier_id  
        FROM  
            courier_data  
        WHERE  
            delivery_success_rate > 95  
    );          

The subquery identifies couriers with high success rates, and the main query filters their orders.

4. Aggregating Data with HAVING

Why Use HAVING? Unlike WHERE, HAVING filters aggregated data, making it invaluable for refining grouped results.

Real-Life Use Case: A sports analytics company needs to identify teams with an average player salary exceeding a specific threshold.

Example Query:

SELECT  
    team_id,  
    AVG(salary) AS avg_salary  
FROM  
    players  
GROUP BY  
    team_id  
HAVING  
    AVG(salary) > 500000;          

This query identifies high-salary teams for budget planning and strategy.

5. Pivoting Data for Better Visualization

What Is Pivoting? Pivoting transforms rows into columns, allowing for compact, visually intuitive data summaries.

Real-Life Use Case: A company tracks sales across multiple regions. Pivoting organizes data into a clear regional comparison.

Example Query:

SELECT  
    region,  
    SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) AS sales_2023,  
    SUM(CASE WHEN year = 2024 THEN sales ELSE 0 END) AS sales_2024  
FROM  
    sales_data  
GROUP BY  
    region;          

This query outputs side-by-side comparisons of sales performance by region.

Why These Techniques Matter

Mastering advanced SQL techniques isn’t just about writing cleaner code—it’s about solving problems efficiently and delivering actionable insights. Whether you’re running a business or advancing your career, these skills set you apart in the data analytics space.

Data No Doubt! Check out WSDALearning.ai and start learning Data Analytics and Data Science Today!