Chapter 20:. Advanced Topics
The grand finale of our SQL journey!
By now you’re already writing solid, production-ready queries. This chapter is where we go pro-level: features that make analysts, data engineers, and backend developers say “wow, that’s elegant!”
We’re covering five advanced topics that appear in almost every serious job interview and real-world project:
- Window functions (ROW_NUMBER, RANK, LEAD, LAG, etc.)
- Common Table Expressions (CTE)
- Pivot / Unpivot
- SQL injection and basic security
- Performance tuning basics
We’ll use our familiar bookshop database (books and authors tables) and add a bit more data for realistic examples.
1. Window Functions – Analytics Without Grouping
Window functions let you perform calculations across a set of rows (a “window”) related to the current row, without collapsing the result into one row like GROUP BY does.
They are incredibly powerful for rankings, running totals, comparisons with previous/next rows, etc.
Common window functions:
| Function | What it does | Example Use Case |
|---|---|---|
| ROW_NUMBER() | Assigns unique sequential number | Ranking rows uniquely |
| RANK() | Assigns rank (same rank for ties, skips next) | Ranking sales with ties |
| DENSE_RANK() | Assigns rank (same rank for ties, no skip) | Ranking without gaps |
| NTILE(n) | Divides rows into n buckets | Quartiles, deciles |
| LEAD() | Value from the next row | Compare current vs next |
| LAG() | Value from the previous row | Compare current vs previous |
| SUM() OVER() | Running/cumulative total | Running total sales |
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
function() OVER ( [PARTITION BY column] -- groups like GROUP BY [ORDER BY column] -- defines order inside window [ROWS/RANGE ...] -- frame specification ) |
Example 1 – Rank books by price within each author
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT a.name AS author, b.title, b.price, ROW_NUMBER() OVER (PARTITION BY a.author_id ORDER BY b.price DESC) AS row_num, RANK() OVER (PARTITION BY a.author_id ORDER BY b.price DESC) AS rank, DENSE_RANK() OVER (PARTITION BY a.author_id ORDER BY b.price DESC) AS dense_rank FROM books b INNER JOIN authors a ON b.author_id = a.author_id ORDER BY a.name, b.price DESC; |
Result (example):
| author | title | price | row_num | rank | dense_rank |
|---|---|---|---|---|---|
| Chetan Bhagat | 2 States | 299.00 | 1 | 1 | 1 |
| Chetan Bhagat | Half Girlfriend | 280.00 | 2 | 2 | 2 |
| Ruskin Bond | The Room on the Roof | 250.00 | 1 | 1 | 1 |
| Ruskin Bond | The Blue Umbrella | 180.00 | 2 | 2 | 2 |
Example 2 – Running total stock + compare with previous book
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT title, price, stock, SUM(stock) OVER (ORDER BY price DESC) AS running_total_stock, LAG(stock) OVER (ORDER BY price DESC) AS previous_book_stock, LEAD(stock) OVER (ORDER BY price DESC) AS next_book_stock FROM books ORDER BY price DESC; |
Result snippet:
| title | price | stock | running_total_stock | previous_book_stock | next_book_stock |
|---|---|---|---|---|---|
| The Palace of Illusions | 450.00 | 25 | 25 | NULL | 65 |
| The Immortals of Meluha | 349.00 | 65 | 90 | 25 | 399 |
| … | … | … | … | … | … |
2. Common Table Expressions (CTE) – Cleaner, Readable Queries
A CTE (WITH clause) is a temporary named result set that you can reference multiple times in the same query.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name ... |
Example – Find top 3 authors by total stock value
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
WITH AuthorStats AS ( SELECT a.author_id, a.name, SUM(b.price * b.stock) AS total_value, COUNT(b.book_id) AS book_count FROM authors a LEFT JOIN books b ON a.author_id = b.author_id GROUP BY a.author_id, a.name ) SELECT name, book_count, ROUND(total_value, 2) AS total_stock_value, RANK() OVER (ORDER BY total_value DESC) AS rank FROM AuthorStats ORDER BY total_value DESC LIMIT 3; |
Multiple CTEs + recursive (advanced):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH RECURSIVE CategoryHierarchy AS ( SELECT category_id, name, parent_id FROM categories WHERE parent_id IS NULL -- top level UNION ALL SELECT c.category_id, c.name, c.parent_id FROM categories c INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.category_id ) SELECT * FROM CategoryHierarchy; |
CTE makes complex queries readable, reusable, and debuggable.
3. Pivot / Unpivot – Turning Rows into Columns and Vice Versa
Pivot = turn rows into columns (e.g., sales by month as columns) Unpivot = turn columns into rows
MySQL does not have native PIVOT/UNPIVOT (SQL Server & Oracle do), but we can simulate with CASE + GROUP BY.
Example – Pivot: Books per author as columns
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT category, SUM(CASE WHEN author_id = 1 THEN 1 ELSE 0 END) AS 'Ruskin Bond', SUM(CASE WHEN author_id = 3 THEN 1 ELSE 0 END) AS 'Chetan Bhagat', SUM(CASE WHEN author_id = 4 THEN 1 ELSE 0 END) AS 'Amish Tripathi' FROM books GROUP BY category; |
Result (pivot-style):
| category | Ruskin Bond | Chetan Bhagat | Amish Tripathi |
|---|---|---|---|
| Fiction | 1 | 2 | 0 |
| Children | 1 | 0 | 0 |
| Fantasy | 0 | 0 | 1 |
Unpivot example (reverse):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT book_id, 'price' AS metric, price AS value FROM books UNION ALL SELECT book_id, 'stock' AS metric, stock FROM books; |
Modern way (MySQL 8+): Use JSON_TABLE or tools like dbt for true pivoting.
4. SQL Injection and Security Basics
SQL injection is when an attacker inserts malicious SQL into your query through user input.
Example of vulnerable code (NEVER DO THIS):
|
0 1 2 3 4 5 6 |
$sql = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'"; |
User enters: ‘ OR ‘1’=’1 → query becomes:
|
0 1 2 3 4 5 6 |
SELECT * FROM users WHERE username = '' OR '1'='1' |
→ Logs in as first user!
Prevention methods:
- Prepared statements / parameterized queries (best & safest)
PHP01234567$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");$stmt->execute([$_POST['username']]);
- Stored procedures (hide SQL from app)
- Input validation & escaping (as backup)
- Least privilege – DB user should have only needed permissions
- Never concatenate user input into SQL
Other security tips:
- Use LIMIT on public queries
- Hash passwords (never store plain text)
- Use HTTPS
- Audit logs for sensitive tables
5. Performance Tuning Basics – Quick Wins
| Technique | When to use | How to do it |
|---|---|---|
| Indexes | Frequent WHERE/JOIN/ORDER BY | CREATE INDEX on those columns |
| EXPLAIN / EXPLAIN ANALYZE | See how query is executed | EXPLAIN SELECT … |
| **Avoid SELECT *** | Only fetch needed columns | SELECT title, price … |
| Use LIMIT | Pagination, top-N queries | LIMIT 10 OFFSET 20 |
| Filter early | Push WHERE before JOIN | WHERE before INNER JOIN |
| Covering index | Index includes all columns in SELECT | CREATE INDEX ON books (title, price, author_id) |
| Avoid functions on indexed columns | WHERE YEAR(date) = 2025 → slow | WHERE date BETWEEN ‘2025-01-01’ AND ‘2025-12-31’ |
| Statistics up to date | Database optimizer needs current stats | ANALYZE TABLE books; |
Quick checklist:
- Run EXPLAIN on slow queries
- Add missing indexes
- Rewrite query to use indexes
- Check for full table scans (type: ALL in EXPLAIN)
- Consider partitioning for very large tables
