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, readable parts. They are especially useful when a query contains multiple subqueries.

Example using subqueries in a WHERE clause:

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

The same logic rewritten with 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 DISTINCT name FROM toronto_ppl) AND salary >= (SELECT avgSalary FROM avg_female_salary)

CTEs allow you to assign meaningful names (e.g., toronto_ppl, avg_female_salary) to each logical step, making the query easier to understand and maintain. They also enable advanced techniques like recursive queries.

2. Recursive CTEs

A recursive CTE references itself, similar to a recursive function in programming. It is particularly useful for hierarchical data such as organizational charts, file systems, or link graphs.

A recursive CTE has three parts:

Anchor member: the initial query that returns the base result.

Recursive member: the query that references the CTE and joins with the anchor.

Termination condition: stops the recursion.

Example: retrieve each employee's manager ID from a staff table.

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, reduce duplication, and write cleaner code—much like functions in Python.

Example: classify employee seniority based on tenure using a CASE expression directly in the query:

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 encapsulate the 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

The query becomes simpler and the get_seniority function can be reused elsewhere.

4. Pivoting Data with CASE WHEN

CASE WHEN is versatile for conditional logic and can also pivot rows into columns. For instance, transform a table with monthly revenue rows into a wide table with one column per month.

Initial table:

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

Result table (one row per id, columns for each month):

+------+-------------+-------------+-------------+-----+-----------+  | 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 or tables, but with subtle differences: EXCEPT removes duplicates and returns distinct rows; NOT IN does not. EXCEPT requires the same number of columns in both queries; NOT IN compares a single column.

6. Self-Join

A table joined to itself. Useful when data is stored in a single large table rather than multiple smaller ones.

Example: find employees who earn more than their managers.

+----+-------+--------+-----------+  | Id | Name  | Salary | ManagerId |  +----+-------+--------+-----------+  | 1  | Joe   | 70000  | 3         |  | 2  | Henry | 80000  | 4         |  | 3  | Sam   | 60000  | NULL      |  | 4  | Max   | 90000  | NULL      |  +----+-------+--------+-----------+ Answer: SELECT a.Name AS Employee FROM Employee AS a JOIN Employee AS b ON a.ManagerID = b.Id WHERE a.Salary > b.Salary

Only Joe earns more than his manager (Sam).

7. ROW_NUMBER vs RANK vs DENSE_RANK

Ranking functions assign a rank to each row. Common use cases include ranking customers by spending, products by sales, countries by revenue, or videos by views.

Example query on a student_grades table:

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

ROW_NUMBER()

assigns a unique sequential number to each row; ties are broken arbitrarily. RANK() assigns the same rank to tied rows, leaving gaps in the sequence after ties. DENSE_RANK() also assigns the same rank to ties but does not leave gaps (e.g., after two rows ranked 2, the next rank is 3).

8. Calculating Delta Values with LEAD and LAG

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

Compare each month's sales to the previous month:

SELECT month, sales, sales - LAG(sales, 1) OVER (ORDER BY month) FROM monthly_sales

Compare each month's sales to the same month last year (12-month lag):

SELECT month, sales, sales - LAG(sales, 12) OVER (ORDER BY month) FROM monthly_sales

9. Calculating Running Totals

Using SUM() as a window function with an ORDER BY clause produces a cumulative sum.

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

10. DateTime Manipulation

SQL interviews often involve datetime operations such as grouping by time intervals or converting formats.

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

+---------+------------------+------------------+  | Id(INT) | RecordDate(DATE) | Temperature(INT) |  +---------+------------------+------------------+  | 1       | 2015-01-01       | 10               |  | 2       | 2015-01-02       | 25               |  | 3       | 2015-01-03       | 20               |  | 4       | 2015-01-04       | 30               |  +---------+------------------+------------------+ Answer: SELECT a.Id FROM Weather a, Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF(a.RecordDate, b.RecordDate) = 1

Mastering these ten concepts will prepare you for most advanced SQL questions in data science interviews.