10 Essential SQL Concepts for Interview Preparation

The article introduces ten intermediate‑to‑advanced SQL concepts that are frequently tested in data‑analysis and engineering interviews, providing both conceptual explanations and concrete query examples.

1. Common Table Expressions (CTEs) – CTEs create temporary result sets that can be referenced later in a query, making complex sub‑queries easier to read and maintain.

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

CTEs allow you to break a large query into modular parts, improving readability and reusability.

2. Recursive CTEs – By referencing the CTE within itself, recursive CTEs can traverse hierarchical data such as organization charts or graph structures.

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
)

This pattern returns each employee together with the chain of managers above them.

3. Temporary Functions – In platforms like BigQuery you can define a temporary function to encapsulate reusable logic, similar to a Python 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;

Using a temporary function keeps the main query concise and promotes code reuse.

4. CASE WHEN Pivoting – CASE expressions can transform row‑level values into columnar format, useful for creating month‑wise revenue columns.

SELECT id,
       CASE WHEN month = 'Jan' THEN revenue END AS Jan_Revenue,
       CASE WHEN month = 'Feb' THEN revenue END AS Feb_Revenue,
       ...
FROM sales;

This technique pivots a tall table into a wide one.

5. EXCEPT vs NOT IN – Both operators compare two result sets, but EXCEPT removes duplicates and returns rows present in the first query but not the second, while NOT IN performs a row‑wise exclusion.

6. Self‑Join – Joining a table to itself enables queries such as finding employees whose salary exceeds that of their manager.

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

7. Ranking Functions – ROW_NUMBER, RANK, and DENSE_RANK assign ordinal positions to rows based on an ORDER BY clause, differing in how they handle ties.

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 Deltas – The LAG and LEAD window functions let you compare a value with its previous (or next) row, useful for month‑over‑month or year‑over‑year differences.

SELECT month,
       sales,
       sales - LAG(sales, 1) OVER (ORDER BY month) AS month_over_month,
       sales - LAG(sales, 12) OVER (ORDER BY month) AS year_over_year
FROM monthly_sales;

9. Cumulative Totals – Using SUM as a window function computes running totals across an ordered set.

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

10. Date‑Time Manipulation – Functions such as DATE_ADD, DATE_SUB, DATE_TRUNC, and DATEDIFF allow you to adjust, truncate, or compare timestamps, enabling queries like finding days with higher temperature 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;

Mastering these concepts equips interviewees to solve a wide range of real‑world SQL problems efficiently.