SQL Mastery: Advanced Queries and Database Optimization for Analysts and Developers

SQL Mastery: Advanced Queries and Database Optimization for Analysts and Developers

You already know how to write simple SELECT and JOIN? Congratulations, you've passed the basic level. But the real magic of SQL begins where textbooks end: in advanced queries that save hours of work, and in optimization that turns a "heavy" report into an instant solution. In this article, we'll break down key tools—window functions, CTEs, indexes, and query plan analysis. This isn't just theory, but practical techniques needed by everyone who works with data.

Window Functions: Analysis Without Grouping

Window functions are SQL's superpower. They allow you to perform calculations over a set of rows without collapsing them into a single group. For example, you want to see for each salesperson their sales and share of total company sales. Instead of a complex subquery:

SELECT 
    salesperson,
    amount,
    SUM(amount) OVER() AS total_sales,
    amount * 100.0 / SUM(amount) OVER() AS percentage
FROM sales;

Here, SUM(amount) OVER() is a window function that calculates the sum across all rows but doesn't group the result. You get both detailed data and an aggregate in one row. This is indispensable for reports, ranking (ROW_NUMBER, RANK), and moving averages. The efficiency of such queries directly depends on the database structure and indexes.

CTEs: Readability and Recursion

Common Table Expressions (CTEs) are temporary named datasets that make queries as clear as an assembly manual. Instead of nested subqueries:

WITH high_value_orders AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
)
SELECT c.name, h.total
FROM customers c
JOIN high_value_orders h ON c.id = h.customer_id;

But the real highlight is recursive CTEs. They are perfect for hierarchical data: category trees, organizational structures, nested comments. Recursion in SQL is a powerful but rare skill that sets an expert apart.

Indexes: The Heart of Performance

Without indexes, even a simple SELECT can turn into a full table scan. Indexes are like a table of contents in a book: you don't flip through 500 pages, but go straight to the chapter. Main types:

A common mistake is putting indexes on every column. This slows down inserts and updates. Database optimization requires analysis: which queries are most frequent? Which columns are in WHERE and JOIN? Sometimes a single composite index (on multiple columns) solves the problem better than three single-column indexes.

Query Plans: Peeking Under the Hood

The query execution plan (EXPLAIN) is an X-ray of your SQL. It shows how the database executes the query: whether it uses indexes, how many rows it reads, what operations (Seq Scan, Index Scan, Nested Loop). Here's an example analysis:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

You'll see:
- Seq Scan (full scan) — bad for large tables.
- Index Scan — great if there's an index.
- Bitmap Heap Scan — good for selecting a large percentage of rows.

Understanding the plan allows you to identify bottlenecks. For example, if a query performs a Seq Scan on a table with a million records, it's time to add an index. Or if you see a Nested Loop with many iterations, you might need to rewrite the query as a JOIN.

Performance Optimization: Practical Tips

1. **Avoid SELECT *** — select only the columns you need. This reduces I/O load.
2. Use LIMIT for testing queries — don't overload the server with unnecessary data.
3. Table partitioning — split large tables by