Advanced SQL Querying

Beyond Aggregation: Window Functions

You already know how to use aggregate functions like COUNT() and SUM() with GROUP BY to collapse multiple rows into a single summary row. But what if you want to perform calculations across a set of rows while keeping the individual rows intact? That's where window functions come in.

A window function calculates a value over a set of rows that are related to the current row. Unlike GROUP BY, it doesn't merge those rows. It returns a value for each row based on the "window" of data it looks at.

Let's look at ranking. Say we have a table of employees and want to rank them by salary within each department. GROUP BY can't help here, but RANK() can.

SELECT 
  employee_name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

The magic happens in the OVER() clause. PARTITION BY department defines the window – in this case, all employees in the same department. ORDER BY salary DESC tells the function how to order the rows within that window before assigning a rank. The result is a new column, dept_rank, that shows each employee's salary rank right alongside their details.

Use ROW_NUMBER() instead of RANK() if you need a unique number for each row, even for ties. RANK() will assign the same rank to rows with the same value, creating gaps in the sequence (e.g., 1, 2, 2, 4).

Window functions are also perfect for period-over-period analysis. The LEAD() and LAG() functions let you access data from a subsequent or previous row in your window, respectively. This is incredibly useful for calculating growth.

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

Here, the window is the entire result set, ordered by month. For each row, LAG(monthly_sales, 1) looks back one row (1) and pulls the monthly_sales value from it. You can now easily calculate the month-on-month change in a single query.

Readable Code with CTEs

As queries become more complex, they can become difficult to read, with subqueries nested inside other subqueries. Common Table Expressions (CTEs) solve this by letting you define temporary, named result sets that exist only for the duration of a single statement. Think of them as giving a name to a subquery.

A CTE is defined using the WITH keyword. Let's refactor our sales growth query to use one.

WITH MonthlySales AS (
  SELECT
    DATE_TRUNC('month', order_date)::DATE AS sale_month,
    SUM(order_total) AS monthly_sales
  FROM orders
  GROUP BY 1
)

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

First, we create a CTE named MonthlySales that aggregates our raw orders into monthly totals. Then, the main query simply selects from this clean, temporary table. The logic is separated into logical steps, making the entire query much easier to understand, debug, and maintain.

Advanced Joins

Beyond the standard INNER and LEFT joins, several other join types are essential for complex analysis.

A Self-Join is when you join a table to itself. This is useful for querying hierarchical data stored in a single table, like an employee table that includes a manager_id column referencing another employee's ID.

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

Notice we use table aliases (e for employee, m for manager) to distinguish between the two instances of the employees table.

A Cross-Join creates a Cartesian product of two tables, matching every row from the first table with every row from the second. While this can generate massive result sets and should be used with caution, it's perfect for creating combinations, like all possible sizes and colours for a product.

SELECT 
  s.size_name,
  c.color_name
FROM sizes s
CROSS JOIN colors c;

Finally, an Anti-Join is a pattern for finding rows in one table that do not have a match in another. A common use case is finding customers who have never placed an order. The most common way to write this is with a LEFT JOIN and a WHERE clause that filters for NULL on the key from the second table.

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This works because the LEFT JOIN keeps all customers, but for those with no orders, all columns from the orders table (including order_id) will be NULL.

Dynamic Queries and Pivoting

A subquery, or inner query, is a query nested inside another SQL statement. They are powerful for dynamic filtering. For example, to find all products with a price higher than the average price:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

A correlated subquery is an inner query that depends on the outer query for its values. It's evaluated once for each row processed by the outer query. This can be less performant but is useful for certain problems, like finding employees whose salary is above the average for their specific department.

SELECT e1.employee_name, e1.salary, e1.department
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

Notice the inner query references e1.department from the outer query, making it correlated.

Lastly, sometimes you need to pivot data, turning unique values from one column into multiple columns in the output. While some SQL dialects have a PIVOT function, a universal method is conditional aggregation using CASE WHEN.

Imagine you have sales data in a table with columns sale_year and amount, and you want to see total sales for each year in separate columns.

SELECT
  customer_id,
  SUM(CASE WHEN sale_year = 2022 THEN amount ELSE 0 END) AS sales_2022,
  SUM(CASE WHEN sale_year = 2023 THEN amount ELSE 0 END) AS sales_2023,
  SUM(CASE WHEN sale_year = 2024 THEN amount ELSE 0 END) AS sales_2024
FROM sales
GROUP BY customer_id;

Here, SUM acts only on the values that meet the CASE WHEN condition for each new column, effectively rotating the data from a vertical to a horizontal format.

Quiz Questions 1/6

What is the primary difference between a window function and a GROUP BY clause in SQL?

Quiz Questions 2/6

You need to find all customers in your customers table who have never placed an order in the orders table. Which type of join pattern is best suited for this task?

These advanced techniques transform SQL from a simple data retrieval tool into a powerful analytical engine. By combining them, you can answer complex business questions directly within the database.

Data Analyst Professional Path