Advanced SQL Querying

Writing Smarter Queries

You've already mastered the basics of pulling data with SELECT and combining tables with JOIN. But real-world analysis often requires more than just fetching raw data. You need to perform multi-step calculations, compare values across different rows, and rank items within categories. This is where advanced SQL techniques turn your queries from simple data requests into powerful analytical tools.

Let's move beyond foundational syntax and explore how to structure complex logic and perform sophisticated analysis directly within your database.

Modular Logic with CTEs

As your queries grow, they can become a tangled mess of nested subqueries, making them hard to read, debug, and maintain. Common Table Expressions (CTEs) solve this problem by letting you create temporary, named result sets that you can reference within a larger query. Think of a CTE as giving a name to a step in your recipe. Instead of writing out the entire instruction for 'sifted flour mixture' every time, you just refer to it by name.

WITH RegionalSales AS (
  SELECT
    region,
    SUM(sale_amount) AS total_sales
  FROM sales
  GROUP BY region
),
TopRegions AS (
  SELECT region
  FROM RegionalSales
  WHERE total_sales > 1000000
)
SELECT
  s.region,
  s.customer_id,
  s.sale_amount
FROM sales s
JOIN TopRegions tr ON s.region = tr.region;

In this example, we create two CTEs: RegionalSales calculates the total sales for each region, and TopRegions uses that result to identify regions exceeding $1 million in sales. The final SELECT statement then joins back to the original sales table to pull details for only those top-performing regions. Each logical step is separate and clear. You can chain as many CTEs as you need, with each one building on the last, creating a clean, readable workflow.

Advanced Analysis with Window Functions

Aggregate functions like SUM() and COUNT() are useful, but they collapse your data into a single row. What if you want to calculate a running total or rank employees within their department without losing the detail of each row? Window functions let you do this. They perform calculations across a set of rows related to the current row, defined by the OVER() clause.

The power of the OVER() clause comes from two key components: PARTITION BY and ORDER BY.

PARTITION BY divides the rows into groups, or 'windows'. The function then operates independently within each partition. It's like GROUP BY, but it doesn't collapse the rows.
ORDER BY determines the order of rows within each partition, which is crucial for functions that depend on sequence, like calculating a running total or finding the previous value.

Think of PARTITION BY as creating separate playing fields for your function to run on.

Ranking and Trend Analysis

Ranking is a common analytical task. SQL provides three main functions for this: ROW_NUMBER(), RANK(), and DENSE_RANK(). They all assign a rank to rows based on a specified order, but they handle ties differently.

Function	Behavior with Ties (e.g., two people tied for 2nd)
`ROW_NUMBER()`	Assigns a unique, consecutive number. Ranks are 1, 2, 3, 4... No duplicates.
`RANK()`	Assigns the same rank to ties, but then skips the next rank(s). Ranks: 1, 2, 2, 4.
`DENSE_RANK()`	Assigns the same rank to ties, but doesn't skip ranks. Ranks: 1, 2, 2, 3.

-- Find the top 3 highest-paid employees in each department
WITH RankedEmployees AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as rn
  FROM employees
)
SELECT
  name,
  department,
  salary
FROM RankedEmployees
WHERE rn <= 3;

Beyond ranking, you often need to compare a row to its neighbours. LAG() and LEAD() are perfect for this. LAG() accesses data from a previous row in the partition, while LEAD() accesses data from a subsequent row. This is incredibly useful for calculating period-over-period changes, like month-over-month revenue growth.

-- Calculate month-over-month sales growth
WITH MonthlySales AS (
    SELECT
        DATE_TRUNC('month', order_date)::DATE AS sales_month,
        SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY 1
)
SELECT
    sales_month,
    total_sales,
    LAG(total_sales, 1) OVER (ORDER BY sales_month) AS previous_month_sales,
    (total_sales - LAG(total_sales, 1) OVER (ORDER BY sales_month)) / LAG(total_sales, 1) OVER (ORDER BY sales_month) AS growth_rate
FROM MonthlySales;

You can also use aggregate functions like SUM() and AVG() as window functions. For example, SUM(sales) OVER (PARTITION BY department ORDER BY sale_date) would create a running total of sales within each department.

Peeking Under the Hood

Writing a clever query is one thing; writing one that runs efficiently on millions of rows is another. When a query is slow, you need to understand how the database is executing it. Most SQL databases provide a command, often EXPLAIN or EXPLAIN ANALYZE, to show you the query execution plan.

The execution plan is the database's roadmap for fetching your data. It details the steps it will take, such as whether it will use an index, the order it will join tables, and the type of join algorithm it will employ. Reading these plans can be daunting at first, but learning to spot inefficiencies is a critical skill for any serious data professional. A poorly written query that works on a small dataset can bring a production system to its knees when data volumes grow.

Quiz Questions 1/6

What is the primary advantage of using a Common Table Expression (CTE) in a complex SQL query?

Quiz Questions 2/6

How does a window function differ from a standard aggregate function like SUM() used with a GROUP BY clause?

With CTEs and window functions, you can tackle complex analytical tasks with SQL code that is both powerful and easy to understand.

Professional Data Analytics Mastery