Master 10 Essential Advanced SQL Concepts Every Data Professional Needs

As data volumes grow, the demand for skilled SQL professionals increases, especially those fluent in intermediate‑to‑advanced concepts.

1. Common Table Expressions (CTEs)

CTEs create temporary result sets that can be referenced within a SELECT, allowing modular and readable queries.

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"
);

Using CTEs simplifies queries with multiple subqueries.

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, useful for hierarchical data such as organization charts.

Anchor member: base query returning the initial result set.

Recursive member: query that references the CTE.

Termination condition: stops recursion.

Example retrieving each employee’s manager ID:

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
)

3. Temporary Functions

Temporary functions let you encapsulate reusable logic, similar to functions in programming languages.

Break code into smaller blocks.

Write cleaner SQL.

Avoid duplication and enable reuse.

Example using CASE expression:

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;

Same logic with a temporary function:

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. CASE WHEN Pivoting Data

CASE WHEN can also pivot rows into columns, e.g., turning month values into separate revenue columns.

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

-- Pivoted result
+----+------------+------------+------------+-----+------------+
| id | Jan_Revenue| Feb_Revenue| Mar_Revenue| ... | Dec_Revenue|
+----+------------+------------+------------+-----+------------+
| 1  | 8000       | 7000       | 6000       | ... | null       |
| 2  | 9000       | null       | null       | ... | null       |
| 3  | null       | 10000      | null       | ... | null       |
+----+------------+------------+------------+-----+------------+

5. EXCEPT vs NOT IN

Both compare rows between two queries, but EXCEPT removes duplicates and returns distinct rows, while NOT IN checks for non‑membership.

6. Self‑Join

A self‑join links a table to itself, useful for hierarchical relationships such as employees and their managers.

Example: find employees earning more than their manager.

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 with different handling of ties.

ROW_NUMBER(): unique sequential numbers.

RANK(): same rank for ties, leaves gaps.

DENSE_RANK(): same rank for ties, no 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 Delta Values

Use LAG or LEAD to compare a value with its previous (or next) row, e.g., month‑over‑month sales.

# Compare each month’s sales to last month
SELECT month,
       sales,
       sales - LAG(sales, 1) OVER (ORDER BY month) AS delta
FROM monthly_sales;

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

9. Calculating Running Totals

SUM() as a window function computes cumulative totals.

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

10. Date‑Time Manipulation

Common functions include EXTRACT, DATE_ADD, DATE_SUB, DATE_TRUNC, etc., useful for grouping or reformatting dates.

Example: find dates where temperature is higher than the previous day.

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

Thank you for reading!