SQL Optimization

Beyond the Basics: Writing Smarter SQL

You already know how to pull data with SELECT and combine tables with JOIN. Now it's time to write queries that don't just work, but work efficiently. As datasets grow into millions or billions of rows, especially in fields like fintech, a poorly written query can be the difference between a real-time insight and a server timeout. The key is moving computation closer to the data, letting the database engine do the heavy lifting in the most efficient way possible.

One of the best ways to clean up complex logic is with Common Table Expressions, or CTEs. Instead of nesting subqueries inside other subqueries, which can become a nightmare to read and debug, CTEs let you define temporary, named result sets. Think of them as variables for your query.

You define a CTE using the WITH keyword. This makes your main query logic clean and modular. For example, let's find all customers who spent more than the average in Q1.

WITH Q1Sales AS (
  -- First, define the total sales for each customer in Q1
  SELECT
    customer_id,
    SUM(amount) AS total_spent
  FROM transactions
  WHERE transaction_date BETWEEN '2023-01-01' AND '2023-03-31'
  GROUP BY customer_id
), 
AverageSpend AS (
  -- Next, calculate the average spend across all customers in that period
  SELECT AVG(total_spent) AS q1_avg
  FROM Q1Sales
)
-- Finally, use the CTEs to find the customers who exceed the average
SELECT
  c.customer_name,
  s.total_spent
FROM customers c
JOIN Q1Sales s ON c.id = s.customer_id
CROSS JOIN AverageSpend -- Since AverageSpend is just one row, a CROSS JOIN is fine
WHERE s.total_spent > AverageSpend.q1_avg;

Notice how each logical step is a separate, named block. This is far more readable than a deeply nested subquery.

The Power of Window Functions

For complex analysis like ranking or comparing rows, self-joins can be slow and clumsy. This is where window functions shine. They perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they don't collapse rows; they return a value for each one.

The magic happens with the OVER() clause. It defines the 'window' of data the function should consider. You can use it to partition data into groups with PARTITION BY, which is like a GROUP BY but for a window function.

Let's look at a few ranking functions:

Function	Description	Tie Handling
`RANK()`	Assigns a rank to each row.	Skips ranks after a tie (e.g., 1, 2, 2, 4).
`DENSE_RANK()`	Similar to `RANK()`, but does not skip ranks.	Ranks are consecutive (e.g., 1, 2, 2, 3).
`ROW_NUMBER()`	Assigns a unique number to each row.	Ranks are always unique (e.g., 1, 2, 3, 4).

Imagine you need to find the top three performing sales reps in each region. Without window functions, this is a complicated query. With them, it's elegant.

WITH RankedSales AS (
  SELECT
    employee_name,
    region,
    sale_amount,
    RANK() OVER (PARTITION BY region ORDER BY sale_amount DESC) as rank_in_region
  FROM sales_data
)
SELECT
  employee_name,
  region,
  sale_amount
FROM RankedSales
WHERE rank_in_region <= 3;

We can also perform analytical comparisons. The LAG() and LEAD() functions let you access data from a previous or subsequent row, respectively. This is perfect for calculating things like month-over-month growth.

Here's how you could calculate the change in monthly revenue for a subscription service:

WITH MonthlyRevenue AS (
  SELECT
    DATE_TRUNC('month', payment_date)::DATE as month,
    SUM(amount) as revenue
  FROM payments
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) as previous_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) as monthly_change
FROM MonthlyRevenue
ORDER BY month;

The NTILE(n) function is another useful tool. It divides the rows in a partition into n ranked groups, or 'tiles'. For example, NTILE(4) would divide your customers into quartiles based on their spending, which is a common task in .

Diagnosing Your Queries

How do you know if your query is actually efficient? You ask the database to show you its plan. The EXPLAIN command reveals the the database intends to use to run your query. It won't actually run the query, it just shows the strategy.

The diagram above shows a simple plan. The database will perform a sequential scan on both tables, meaning it reads them from start to finish, and then joins them. For a huge table, a sequential scan is often a major bottleneck.

To get even more detail, you can use EXPLAIN ANALYZE. This command actually runs the query and gives you the real execution times for each step, alongside the estimates. It's the ultimate tool for finding out exactly where your query is spending its time.

If you’ve gotten this far, you’re probably familiar with using EXPLAIN and EXPLAIN ANALYZE to get insight into what approach Postgres is taking to execute queries and the actual performance of those approaches.

When you see a slow step, like a Seq Scan on a large table where you only need a few rows, the solution is often an index. An is a special data structure that allows the database to find rows much faster. Think of it like the index at the back of a book; instead of reading the whole book to find a topic, you can look it up in the index and go straight to the right page.

If our previous query on the transactions table was slow because of the WHERE clause on customer_id, we could create an index on that column. The most common type is a B-tree index, which is great for range queries (>, <, BETWEEN) and equality checks.

-- Create an index on the customer_id column of the transactions table
CREATE INDEX idx_transactions_customer_id ON transactions (customer_id);

After creating this index, running EXPLAIN again would likely show a much faster Index Scan instead of a Seq Scan on that table. Choosing the right columns to index is a critical skill. Good candidates are columns frequently used in WHERE clauses and JOIN conditions.

Advanced Joins and Hierarchies

Sometimes you need to join a table to a function that generates rows. A standard JOIN can't do this. A LATERAL join can. It allows a subquery on the right side of the join to reference columns from the table on the left side. This is extremely useful for 'top-N-per-category' problems.

For example, to find the 3 most recent transactions for each customer:

SELECT
  c.customer_name,
  t.transaction_id,
  t.amount,
  t.transaction_date
FROM customers c
LEFT JOIN LATERAL (
  SELECT
    transaction_id, amount, transaction_date
  FROM transactions
  WHERE transactions.customer_id = c.id -- Correlates with the outer table
  ORDER BY transaction_date DESC
  LIMIT 3
) t ON TRUE;

This query effectively runs the subquery for each and every row in the customers table, giving you the top 3 transactions for each one. It's a powerful pattern for complex reporting.

Ready to test your knowledge? Let's see how well you've grasped these optimization techniques.

Quiz Questions 1/6

What is the primary benefit of using a Common Table Expression (CTE) with the WITH keyword in a complex SQL query?

Quiz Questions 2/6

A key difference between a window function (e.g., RANK() OVER(...)) and a standard aggregate function (e.g., COUNT(*) ... GROUP BY) is that the window function does not collapse the rows of the original table.

Mastering these techniques will elevate your SQL from simple data retrieval to sophisticated, high-performance data engineering. You'll be able to build complex analytical features and diagnose performance issues with confidence.

Comprehensive Data Analysis Mastery