10 Essential Intermediate to Advanced SQL Concepts

1. Common Table Expressions (CTEs)

CTEs allow you to define temporary result sets that can be referenced within a SELECT, making complex sub‑queries easier to read and modularize.

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 second example shows how to break the query into two CTEs—one for Toronto residents and another for the average female salary—and then join them.

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 FROM toronto_ppl) AND salary >= (SELECT avgSalary FROM avg_female_salary);

2. Recursive CTEs

Recursive CTEs reference themselves, similar to recursive functions in programming, and are useful for hierarchical data such as organization charts.

They consist of three parts: an anchor query, a recursive member, and a termination condition.

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 inside a query, improving readability and avoiding repetition.

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;

The same logic can be expressed as 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. Pivoting Data with CASE WHEN

CASE WHEN can be used to transform rows into columns, for example turning a month column into separate revenue columns for each month.

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

-- Desired 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 operators compare rows between two queries, but EXCEPT removes duplicates and returns rows that appear only in the first query, while NOT IN filters rows that match a list of values; subtle differences arise when dealing with NULLs and column counts.

6. Self‑Join

A self‑join joins a table to itself, useful when the data resides in a single large table.

Example: find employees whose salary is higher than their manager’s salary.

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. ROW_NUMBER gives a unique sequential number, RANK gives the same number for ties and leaves gaps, while DENSE_RANK gives the same number for ties without gaps.

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

8. Calculating Delta Values

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

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

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

9. Cumulative Totals

The SUM() window function can compute a running total.

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.

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

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

Conclusion

These ten concepts equip SQL practitioners with powerful tools for writing cleaner, more efficient, and more expressive queries.