You already know how to pull data from a database using SELECT, FROM, and WHERE. Those are the building blocks. But real-world data problems are often messy and complex. Answering questions like "Who are the top 5 performing salespeople in each region?" or "What is the 7-day moving average of sales?" requires more sophisticated tools.

This is where advanced SQL techniques come in. We'll explore two powerful concepts that let you write cleaner, more efficient, and more insightful queries: Common Table Expressions (CTEs) and Window Functions. They allow you to structure complex logic and perform calculations that would be difficult or impossible with basic queries alone.

Think of a Common Table Expression, or CTE, as a temporary, named table that exists only for the duration of a single query. It's a way to break down a complicated problem into logical, readable steps. Instead of nesting subqueries inside other subqueries, which can quickly become a tangled mess, you can define each logical step as a separate CTE.

This makes your code dramatically easier to read, debug, and maintain. You define a CTE using the WITH clause at the beginning of your query.

Let's say we want to find all employees who earn more than the average salary for their department. Without a CTE, you might use a subquery in the WHERE clause. With a CTE, the logic becomes clearer.

In this example, DepartmentAverage is our CTE. It calculates the average salary for each department. The main query then joins the employees table to this temporary DepartmentAverage table to filter for the employees we want. The logic is separated into two distinct, understandable parts.

Window functions are a game-changer for data analysis in SQL. Like aggregate functions (SUM, AVG, etc.), they perform a calculation across a set of rows. However, unlike aggregates, they don't collapse those rows into a single output row. Instead, they return a value for every single row based on a 'window' of related rows.

This window is defined by the OVER() clause. Inside this clause, PARTITION BY is the key. It divides the rows into groups, or partitions. The window function is then applied independently to each partition. Think of it as running a GROUP BY without losing the original rows.

Let's see how we can rank employees within each department based on their salary.

Here, PARTITION BY department_name tells SQL to create a separate window for each department. Within each of those windows, ORDER BY salary DESC sorts the employees from highest to lowest salary. The RANK() function then assigns a rank to each employee within their department.

Two important ranking functions are RANK() and ROW_NUMBER().

• RANK(): Assigns the same rank to rows with the same value in the ORDER BY clause, leaving gaps in the sequence. For example, if two employees tie for 2nd place, the ranks would be 1, 2, 2, 4.
• ROW_NUMBER(): Assigns a unique, sequential number to each row within its partition, regardless of ties. The ranks would be 1, 2, 3, 4.

Window functions aren't just for ranking. They're incredibly powerful for analysing sequences and time-series data. Functions like LEAD and LAG allow you to access data from other rows within the same window.

• LAG(): Accesses data from a previous row in the partition.
• LEAD(): Accesses data from a subsequent row in the partition.

This is perfect for calculating things like year-over-year growth or the time between events.

This query retrieves each day's revenue alongside the previous day's revenue, making it simple to calculate day-over-day changes. LAG(daily_revenue, 1) looks back one row, as specified by the ORDER BY sale_date clause.

To calculate a 7-day moving average of sales, you can define a window that includes the current row and the six preceding rows.

The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW clause defines the exact size of the window for the AVG function at each row, giving you a rolling calculation.

Writing a query that works is one thing; writing one that runs efficiently on large datasets is another. While CTEs improve readability, they don't always guarantee better performance. Sometimes, the database optimiser can handle a complex subquery more efficiently.

A key principle of optimisation is to reduce the amount of data being processed at each step. Filter data as early as possible using WHERE clauses. Ensure your JOIN conditions use indexed columns, as this dramatically speeds up lookups between tables.

Databases provide tools to analyse how your query is executed. In PostgreSQL and MySQL, you can use EXPLAIN or EXPLAIN ANALYZE before your SELECT statement. This returns the 'query plan', which is the step-by-step recipe the database follows to get your data. Learning to read these plans is a crucial skill for identifying bottlenecks, such as a full table scan where an index scan would be faster.

When you encounter performance issues, check your query plan. Are joins happening in a logical order? Are indexes being used effectively? Sometimes, rewriting a query to use a different type of join or breaking it into smaller steps with temporary tables can lead to significant performance gains.

Time to test your knowledge of these advanced techniques.

Mastering these advanced SQL patterns moves you from simply retrieving data to actively analysing and deriving insights from it. They are essential tools for any serious data professional.