Advanced SQL Querying

Beyond Simple Aggregates

You're comfortable with GROUP BY for summarizing data. It's a powerful tool, but it has a limitation: it collapses your rows. When you calculate the average salary per department, you lose the individual employee data. What if you want to see an employee's salary and the average for their department in the same row? This is where come in. They perform calculations across a set of rows, much like GROUP BY, but they don't collapse the result. Each row maintains its identity and gets a new value based on the calculation.

Think of it like standing in a line. A GROUP BY would just tell you the average height of everyone in the line. A window function can tell you your own height, plus the height of the person in front of you and the person behind you, all at once. The magic happens within the OVER() clause, which defines the 'window' of rows the function should consider. Inside OVER(), you'll use PARTITION BY to create separate windows (like 'per department') and ORDER BY to arrange the rows within that window.

Ranking and Navigating Rows

Two of the most common uses for window functions are ranking and numbering rows. ROW_NUMBER() assigns a unique, sequential integer to each row within its window. RANK(), on the other hand, assigns a rank based on a value. If two rows have the same value, they get the same rank, and a gap is left in the sequence. For example, if two employees tie for 2nd place, the next rank will be 4th.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

-- If two employees in 'Sales' have the same top salary:
-- row_num for them will be 1 and 2.
-- salary_rank for both will be 1.

Beyond ranking, you can navigate between rows. LAG() lets you look at a previous row, while LEAD() lets you peek at a future one. This is incredibly useful for calculating period-over-period changes, like comparing this month's sales to last month's.

SELECT
    sale_month,
    monthly_sales,
    LAG(monthly_sales, 1) OVER(ORDER BY sale_month) AS previous_month_sales
FROM monthly_reports;

You can also use familiar aggregate functions like SUM() and AVG() as window functions. By defining the window with ORDER BY, you can create running totals or moving averages. A running total sums up values from the start of the window to the current row. A moving average calculates the average over a specified number of preceding rows, which helps smooth out noisy data.

\text{Moving Avg} = \frac{\sum_{i=n-k+1}^{n} \text{Value}_i}{k}

SELECT
    sale_date,
    daily_revenue,
    -- Running total of revenue since the beginning
    SUM(daily_revenue) OVER(ORDER BY sale_date) as running_total,
    
    -- 3-day moving average of revenue
    AVG(daily_revenue) OVER(ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3_day
FROM daily_sales;

Organising Complex Logic

As queries become more complex, they can become hard to read. A query with multiple subqueries nested inside each other is difficult to follow and debug. (CTEs) solve this problem. A CTE lets you define a temporary, named result set that you can reference later in your main query. You define it using the WITH keyword.

Think of CTEs as variables for tables. You create a clean, logical building block and give it a name, then use that name in the next step of your calculation. This makes your logic sequential and much easier to understand.

WITH DepartmentSalaries AS (
    -- First, calculate total salary per department
    SELECT
        department,
        SUM(salary) as total_dept_salary
    FROM employees
    GROUP BY department
),
RankedDepartments AS (
    -- Then, rank departments by their total salary
    SELECT 
        department,
        total_dept_salary,
        RANK() OVER (ORDER BY total_dept_salary DESC) as dept_rank
    FROM DepartmentSalaries
)
-- Finally, select the top 3 ranked departments
SELECT
    department,
    total_dept_salary
FROM RankedDepartments
WHERE dept_rank <= 3;

The example above is far cleaner than nesting two subqueries. While subqueries can sometimes be slightly faster because the database optimizer has more freedom, CTEs are generally preferred for complex logic. The performance difference is often negligible, and the improvement in readability and maintainability is huge.

Advanced Joins and Logic

You're familiar with joining tables on exact key matches (equi-joins). But sometimes you need to join data based on other conditions. A is when you join a table to itself. This is essential for querying hierarchical data, like finding an employee's manager when both are stored in the same employees table.

SELECT
    e.employee_name,
    m.employee_name AS manager_name
FROM
    employees e
LEFT JOIN
    employees m ON e.manager_id = m.employee_id;

Finally, you can embed conditional logic directly into your aggregations using CASE WHEN. This allows you to create powerful, flexible summaries. For instance, instead of just counting all orders, you could count orders for different regions in separate columns within a single query, without needing multiple WHERE clauses.

SELECT
    customer_id,
    SUM(CASE WHEN order_status = 'SHIPPED' THEN 1 ELSE 0 END) AS shipped_orders,
    SUM(CASE WHEN order_status = 'RETURNED' THEN 1 ELSE 0 END) AS returned_orders,
    AVG(CASE WHEN product_category = 'Electronics' THEN order_value ELSE NULL END) AS avg_electronics_order
FROM orders
GROUP BY customer_id;

These advanced techniques transform SQL from a simple data retrieval tool into a powerful engine for analysis. By mastering them, you can perform complex calculations and data shaping directly in the database, where it's most efficient.

Professional Data Analyst Path