Advanced SQL Concepts

These concepts are used in analytics engineering, data warehousing, and large-scale production databases.

1. Common Table Expressions (CTEs)

A CTE is a temporary named result set used within a single query.

Basic CTE

WITH high_value_orders AS ( SELECT order_id, customer_id, total_amount FROM orders WHERE total_amount > 500 ) SELECT * FROM high_value_orders;

Why use CTEs?

• Improve readability

• Break complex queries into steps

• Reuse intermediate logic

• Recursively model hierarchies (recursive CTEs)

Recursive CTE example (organizational tree)

WITH RECURSIVE org AS ( SELECT emp_id, manager_id, name, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.emp_id, e.manager_id, e.name, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.emp_id ) SELECT * FROM org;

2. Window Functions

Window functions compute values across related rows, without collapsing groups.

Syntax:

<function>() OVER (PARTITION BY ... ORDER BY ...)

ROW_NUMBER()

SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn FROM orders;

RANK()

Gives ties the same rank.

SELECT product_id, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank FROM product_sales;

Running total

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM sales;

7-day rolling window

SELECT order_date, SUM(amount) OVER ( ORDER BY order_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW ) AS rolling_7_day_sales FROM sales;

3. Pivot / Unpivot Operations

Pivot (rows → columns)

Example: convert monthly sales into columns.

(PostgreSQL using FILTER)

SELECT product_id, SUM(amount) FILTER (WHERE month = 'Jan') AS jan_sales, SUM(amount) FILTER (WHERE month = 'Feb') AS feb_sales, SUM(amount) FILTER (WHERE month = 'Mar') AS mar_sales FROM monthly_sales GROUP BY product_id;

Unpivot (columns → rows)

SELECT product_id, month, amount FROM monthly_sales UNPIVOT ( amount FOR month IN (jan, feb, mar) );

(SQL Server/Oracle support UNPIVOT; MySQL/Postgres require UNION ALL.)

4. Handling Large Datasets

Techniques:

✔ LIMIT + OFFSET (for paging)

✔ Chunked updates (avoid locking)

✔ Indexing frequently filtered columns

✔ Avoid SELECT * on large tables

✔ Archive old data into history tables

Query pattern for large scans:

SELECT * FROM huge_table WHERE indexed_column > 10000 LIMIT 5000;

5. ACID Properties & Isolation Levels

ACID

Isolation Levels

Example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Higher levels = more safety, but less concurrency.

6. Partitioned Tables

Used for huge tables (millions–billions of rows).

Benefits:

• Faster queries

• Faster deletes (drop partitions)

• Improved vacuuming/maintenance

• Better indexing at partition level

Example: partition by month (PostgreSQL)

CREATE TABLE sales ( id SERIAL, sale_date DATE, amount NUMERIC ) PARTITION BY RANGE (sale_date);

Then create partitions:

CREATE TABLE sales_2024_01 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

7. JSON Fields (PostgreSQL/MySQL)

Modern SQL supports JSON columns.

PostgreSQL example

SELECT data->>'first_name' AS first_name, data->'preferences'->>'theme' AS theme FROM users;

Indexing JSON (important for speed)

CREATE INDEX idx_user_preferences ON users USING GIN (data jsonb_path_ops);

MySQL Example

SELECT JSON_EXTRACT(profile, '$.hobbies[0]') AS first_hobby FROM users;

Practical Exercises

Exercise 1 — Use ROW_NUMBER to remove duplicates

Goal: Keep only the newest order per customer.

WITH ordered AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC ) AS rn FROM orders ) DELETE FROM orders WHERE order_id IN ( SELECT order_id FROM ordered WHERE rn > 1 );

Exercise 2 — Rolling 7-day sales report

SELECT order_date, SUM(amount) OVER ( ORDER BY order_date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW ) AS rolling_7_day_sales FROM sales;

This gives for every day:

• total sales for today + previous 6 days

Perfect for dashboards, forecasting, or trend monitoring.