Master 10 Advanced SQL Concepts for Data Science Interviews

As data volumes grow, the demand for professionals fluent in SQL—especially at an intermediate to advanced level—continues to rise, making mastery of key concepts crucial for data‑science interviews.

1. Common Table Expressions (CTEs)

CTEs let you define temporary result sets that can be referenced later in a query, improving modularity and readability.

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 readable version using CTEs:

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

2. Recursive CTEs

Recursive CTEs reference themselves, enabling hierarchical queries such as organizational charts or graph traversals.

Anchor member – returns the base rows.

Recursive member – joins the CTE to itself.

Termination condition – stops 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
    JOIN org_structure os ON os.id = sm.manager_id
)
SELECT * FROM org_structure;

3. Temporary Functions (User‑Defined Functions)

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

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;

Using a temporary function to simplify 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. CASE WHEN for Pivoting Data

Beyond conditional logic, CASE WHEN can pivot rows into columns.

Example: transform a month‑wise revenue table into a single row per ID with separate month columns.

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

-- Pivot query
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 in handling duplicates and NULLs. EXCEPT removes duplicates and returns rows present in the first query but not the second, while NOT IN filters rows where a column value is not found in a sub‑query, treating NULLs specially.

6. Self‑Join

A self‑join links a table to itself, useful for hierarchical relationships stored in a single table.

Example: find 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. Rank vs Dense_Rank vs Row_Number

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

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

Illustration:

8. Calculating Delta Values

Use LAG or LEAD to compare a row with a previous or next row, useful for month‑over‑month changes.

# 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. Running Totals

Window SUM() can compute cumulative totals across ordered rows.

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

10. Date‑Time Manipulation

Common functions for handling dates include DATE_ADD, DATE_SUB, DATE_TRUNC, and extracting components.

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

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

Mastering these ten concepts equips you to tackle most SQL challenges encountered in data‑science interview scenarios.