Advanced SQL Querying

Structuring Complex Queries

You already know how to pull data from a database with SELECT, filter it with WHERE, and aggregate it with GROUP BY. But real-world questions are rarely that simple. They often involve multiple steps, conditional rules, and comparisons across different sets of data. To handle this complexity without writing tangled, unreadable queries, we need better tools for structuring our logic.

Enter Common Table Expressions (CTEs). A CTE lets you create a temporary, named result set that you can reference within a larger query. Think of it as creating a mini-view on the fly. This breaks down a complex problem into logical, readable steps, making your SQL easier to write, debug, and maintain.

-- Find all employees who earn more than the company average salary

WITH AverageSalary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)

SELECT 
  e.employee_name,
  e.salary
FROM 
  employees e,
  AverageSalary av
WHERE 
  e.salary > av.avg_sal;

The query above first defines a CTE named AverageSalary. This CTE calculates a single value: the average salary of all employees. The main query then joins the employees table with this CTE, allowing a simple comparison to filter for employees who earn more than the average. Trying to do this without a CTE would require a less intuitive subquery in the WHERE clause.

Analysing Data Across Rows

Aggregate functions like SUM() and AVG() are powerful, but they collapse multiple rows into a single summary row. What if you want to perform a calculation across a set of related rows but still retain the details of each individual row? For this, we use window functions-.

Window functions perform calculations across a specified set of rows called a "window frame". This lets you create rankings, running totals, and year-on-year comparisons without complex self-joins. Every window function call includes an OVER() clause, which defines the window.

PARTITION BY divides the rows into groups, or partitions. The window function is then applied independently to each partition. ORDER BY sorts the rows within each partition. This is crucial for functions that depend on order, like RANK() or LEAD().

-- Rank products by sales within each product category

SELECT
  product_name,
  category,
  sales,
  RANK() OVER (PARTITION BY category ORDER BY sales DESC) as sales_rank
FROM product_sales;

In the above query, RANK() assigns a rank to each product. The PARTITION BY category clause resets the rank for each new category, and ORDER BY sales DESC ensures that within each category, the product with the highest sales gets rank 1.

Other powerful window functions are LEAD() and LAG(). These functions let you access data from a subsequent or previous row within your window, respectively. This is incredibly useful for calculating period-over-period changes, like month-on-month sales growth.

-- Calculate month-on-month sales growth

WITH MonthlySales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    SUM(order_total) AS total_sales
  FROM orders
  GROUP BY 1
)

SELECT
  sales_month,
  total_sales,
  LAG(total_sales, 1) OVER (ORDER BY sales_month) AS previous_month_sales
FROM MonthlySales
ORDER BY sales_month;

Here, LAG(total_sales, 1) fetches the total_sales value from the previous row, which has been ordered by sales_month. This places the current and previous month's sales on the same row, making it easy to calculate growth.

Advanced Joins and Conditional Logic

While you're familiar with INNER and LEFT joins, some situations call for more specialised joins.

Self Join: This is when you join a table to itself. It's perfect for querying hierarchical data within the same table, like finding an employee's manager.
Full Outer Join: This join returns all rows when there is a match in either the left or the right table. It's useful for finding all records from two tables and seeing where the mismatches are.
Cross Join: This returns the Cartesian product of the two tables—every row from the first table is paired with every row from the second. Use this with extreme caution, as it can generate a massive number of rows very quickly!

-- Find each employee and their manager's name

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

Conditional logic is another key part of advanced querying. The CASE statement allows you to implement if-then-else logic directly within your SQL query. This is great for creating custom categories, applying business rules, or transforming data on the fly.

-- Categorise customers based on their total spending

SELECT 
  customer_id, 
  SUM(order_value) AS total_spent,
  CASE
    WHEN SUM(order_value) > 1000 THEN 'Gold'
    WHEN SUM(order_value) > 500  THEN 'Silver'
    ELSE 'Bronze'
  END AS customer_tier
FROM orders
GROUP BY customer_id;

A Peek Under the Hood

Writing complex queries is one thing; writing fast complex queries is another. The difference often comes down to understanding the execution plan (also known as the query plan). This is the sequence of steps the database decides to use to execute your SQL query.

Most SQL clients have a way to view the execution plan, often using a command like EXPLAIN or EXPLAIN ANALYZE before your SELECT statement. Reviewing the plan can reveal performance bottlenecks. For example, it might show that the database is doing a full table scan when it could be using an index, or that it's chosen an inefficient join order.

While deep optimisation is a topic of its own, getting into the habit of checking the execution plan for your complex queries is a crucial step towards becoming a professional. It helps you understand not just what your query does, but how the database gets it done.

By mastering CTEs, window functions, and advanced joins, you can build a solid data foundation for any analysis, BI tool, or machine learning model.

Professional Data Analysis Workflow