10 Advanced SQL Techniques Every Data Professional Should Master

1. Common Table Expressions (CTEs)

CTEs let you define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, making complex queries easier to read and modular.

SELECT
    name,
    salary
FROM
    People
WHERE
    NAME IN ( SELECT DISTINCT NAME FROM population WHERE country = "Canada" AND city = "Toronto" )
  AND salary >= (
    SELECT AVG(salary)
    FROM salaries
    WHERE gender = "Female"
);

A more elaborate example combines two CTEs—one for Toronto residents and another for the average female salary—to filter people and compare salaries.

WITH toronto_ppl AS (
    SELECT DISTINCT name
    FROM population
    WHERE country = "Canada" AND city = "Toronto"
), avg_female_salary AS (
    SELECT AVG(salary) AS avgSalary
    FROM salaries
    WHERE gender = "Female"
)
SELECT name, salary
FROM People
WHERE name IN (SELECT DISTINCT name FROM toronto_ppl)
  AND salary >= (SELECT avgSalary FROM avg_female_salary);

2. Recursive CTEs

Recursive CTEs reference themselves, similar to recursive functions in programming languages, and are ideal for traversing hierarchical data such as organizational charts or graph structures.

Anchor member: the base query that returns the initial rows.

Recursive member: the query that references the CTE itself to produce subsequent rows.

Termination condition: prevents infinite recursion.

Example: retrieve each employee’s manager chain.

WITH org_structure AS (
    SELECT id, manager_id
    FROM staff_members
    WHERE manager_id IS NULL
    UNION ALL
    SELECT sm.id, sm.manager_id
    FROM staff_members sm
    INNER JOIN org_structure os ON os.id = sm.manager_id
)
SELECT * FROM org_structure;

3. Temporary Functions

Temporary (user‑defined) functions let you encapsulate reusable logic within a query, improving readability and avoiding repetition.

Break complex logic into smaller, testable blocks.

Write cleaner, more maintainable SQL.

Reuse the function across multiple queries, similar to functions in Python.

Example using a CASE expression directly:

SELECT name,
       CASE WHEN tenure < 1 THEN "analyst"
            WHEN tenure BETWEEN 1 AND 3 THEN "associate"
            WHEN tenure BETWEEN 3 AND 5 THEN "senior"
            WHEN tenure > 5 THEN "vp"
            ELSE "n/a"
       END AS seniority
FROM employees;

Creating a temporary function for the same logic:

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (
   CASE WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 AND 3 THEN "associate"
        WHEN tenure BETWEEN 3 AND 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
   END
);
SELECT name, get_seniority(tenure) AS seniority FROM employees;

4. Pivoting Data with CASE WHEN

CASE WHEN can be used not only for conditional logic but also to pivot rows into columns, e.g., turning a month column into separate revenue columns.

Example problem: transform a table with rows for each month into a single row per ID with separate month columns.

-- Input
+----+----------+-------+
| id | revenue  | month |
+----+----------+-------+
| 1  | 8000     | Jan   |
| 2  | 9000     | Jan   |
| 3  | 10000    | Feb   |
| 1  | 7000     | Feb   |
| 1  | 6000     | Mar   |
+----+----------+-------+

-- Pivot using CASE
SELECT id,
       MAX(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
       MAX(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
       MAX(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue
FROM sales
GROUP BY id;

5. EXCEPT vs NOT IN

Both operators compare two result sets, but they differ subtly:

EXCEPT returns rows from the first query that are not present in the second, automatically removing duplicates.

NOT IN filters rows where a column value does not appear in a subquery; it can return NULL‑related semantics.

6. Self‑Join

A self‑join joins a table to itself, useful for hierarchical or comparative queries such as finding employees who earn more than their managers.

SELECT a.Name AS Employee
FROM Employee a
JOIN Employee b ON a.ManagerID = b.Id
WHERE a.Salary > b.Salary;

7. Rank vs Dense_Rank vs Row_Number

These window functions assign ranking numbers to rows based on an ordering.

ROW_NUMBER()

gives a unique sequential number to each row. RANK() assigns the same rank to ties and leaves gaps. DENSE_RANK() also assigns the same rank to ties but without gaps.

SELECT Name, GPA,
       ROW_NUMBER() OVER (ORDER BY GPA DESC) AS row_num,
       RANK()        OVER (ORDER BY GPA DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY GPA DESC) AS dense_rnk
FROM student_grades;

8. Calculating Deltas

Use LAG() or LEAD() to compare a value with its previous (or next) row, useful for month‑over‑month or year‑over‑year differences.

# Compare each month's sales to the previous month
SELECT month,
       sales,
       sales - LAG(sales, 1) OVER (ORDER BY month) AS month_over_month_delta
FROM monthly_sales;

# Compare each month's sales to the same month last year
SELECT month,
       sales,
       sales - LAG(sales, 12) OVER (ORDER BY month) AS year_over_year_delta
FROM monthly_sales;

9. Cumulative Totals

Windowed SUM() computes running totals across ordered rows.

SELECT Month,
       Revenue,
       SUM(Revenue) OVER (ORDER BY Month) AS Cumulative
FROM monthly_revenue;

10. Date‑Time Manipulation

Common functions for handling dates include DATE_TRUNC, DATE_ADD, DATE_SUB, and extracting parts with EXTRACT. They help group, format, or compare temporal data.

Example: find days where temperature was higher than the previous day.

SELECT a.Id
FROM Weather a
JOIN Weather b ON DATEDIFF(a.RecordDate, b.RecordDate) = 1
WHERE a.Temperature > b.Temperature;

These ten techniques empower SQL users to write clearer, more efficient, and analytically richer queries.