10 Advanced SQL Techniques for Data Science Interviews

1. Common Table Expressions (CTEs)

CTEs create temporary result sets that simplify complex queries by breaking them into modular, named blocks. Instead of nesting multiple subqueries in a WHERE clause, you can define each logical step as a separate CTE and reference them by name.

Example: Filtering people from Toronto with salaries above the average female salary.

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

CTEs improve readability, allow variable-like naming, and enable advanced patterns such as recursive queries.

2. Recursive CTEs

A recursive CTE references itself, similar to a recursive function. It is ideal for hierarchical data like organizational charts, file systems, or link graphs. It consists of three parts:

Anchor member: Initial query returning the base result.

Recursive member: Query that references the CTE, joined to the anchor.

Termination condition: Stops recursion when no more rows are produced.

Example: Building an org structure to list each employee's manager.

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 encapsulate reusable logic, reduce duplication, and keep queries clean — analogous to functions in Python. They are created with CREATE TEMPORARY FUNCTION and can be invoked in the main query.

Example: Mapping tenure to seniority level.

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

Beyond conditional assignment, CASE WHEN can pivot rows into columns. For instance, transforming a table with monthly revenue rows into a wide table with one column per month.

Example: Input table with id, revenue, month; output table with columns Jan_Revenue, Feb_Revenue, ….

SELECT id,
  SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
  SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
  ...
FROM revenue_table
GROUP BY id;

5. EXCEPT vs NOT IN

Both compare rows between two queries, but with key differences: EXCEPT removes duplicates and returns distinct rows; NOT IN does not deduplicate. EXCEPT requires the same number and order of columns in both queries; NOT IN compares a single column.

6. Self Joins

A table joins to itself, useful when hierarchical or relational data is stored in a single table. Common pattern: compare an employee's salary to their manager's salary.

Example: Find employees earning 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

Window functions assign rankings differently when ties occur: ROW_NUMBER() assigns a unique sequential number, arbitrarily breaking ties. RANK() assigns the same rank to ties, leaving gaps (e.g., 1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties without gaps (e.g., 1, 2, 2, 3).

Example: Ranking students by GPA.

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

8. Calculating Delta Values

Window functions LAG() and LEAD() access previous or subsequent rows, enabling period-over-period comparisons.

Examples:

-- Month-over-month sales delta
SELECT month, sales,
  sales - LAG(sales, 1) OVER (ORDER BY month) AS mom_delta
FROM monthly_sales;

-- Year-over-year same-month delta
SELECT month, sales,
  sales - LAG(sales, 12) OVER (ORDER BY month) AS yoy_delta
FROM monthly_sales;

9. Calculating Running Totals

Using SUM() with an OVER (ORDER BY ...) clause produces cumulative sums, useful for visualizing growth.

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

10. Datetime Manipulation

Essential functions for handling date/time data include: EXTRACT (e.g., year, month, day) DATE_TRUNC (truncate to a granularity) DATE_ADD, DATE_SUB (arithmetic)

Example: Find dates where 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;

Source: Towards Data Science (https://towardsdatascience.com/ten-advanced-sql-concepts-you-should-know-for-data-science-interviews-4d7015ec74b0)