10 Advanced SQL Techniques for Data Science Interviews

This article presents ten intermediate to advanced SQL concepts essential for data science interviews, adapted from a Towards Data Science post by Nathan Rosidi.

1. Common Table Expressions (CTEs)

CTEs create temporary result sets that simplify complex queries. They modularize code like paragraphs in an article, making queries with multiple subqueries more readable and maintainable. The article contrasts a nested subquery approach with a CTE version that defines toronto_ppl and avg_female_salary separately, then references them in the main query. CTEs also enable advanced techniques such as recursive queries.

2. Recursive CTEs

A recursive CTE references itself, similar to a recursive function in Python. It is especially useful for hierarchical data like organizational charts, file systems, or link graphs. A recursive CTE has three parts: an anchor member (initial query), a recursive member (query that references the CTE), and a termination condition. The example builds an organizational structure by starting with top-level managers ( manager_id IS NULL) and recursively joining staff_members to the CTE on os.id = sm.manager_id.

3. Temporary Functions

Temporary functions break code into reusable blocks, promote clean code, and prevent duplication — analogous to Python functions. The article shows a CASE WHEN expression that maps tenure to seniority levels (analyst, associate, senior, vp). By wrapping this logic in

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (...)

, the main query becomes simpler and the function can be reused.

4. Pivoting Data with CASE WHEN

Beyond conditional assignment, CASE WHEN can pivot rows into columns. The example transforms a table with rows per month into a wide table with one column per month ( Jan_Revenue, Feb_Revenue, etc.) using conditional aggregation.

5. EXCEPT vs NOT IN

Both compare rows between two queries/tables, but with subtle differences: EXCEPT removes duplicates and returns distinct rows, while NOT IN does not. Also, EXCEPT requires the same number of columns in both queries, whereas NOT IN compares a single column.

6. Self Joins

A table joins to itself, which is common when data is stored in one large table rather than many small ones. The example finds employees who earn more than their managers by joining Employee a to Employee b on a.ManagerID = b.Id and filtering a.Salary > b.Salary.

7. Rank vs Dense Rank vs Row Number

Window functions assign ranks to rows. ROW_NUMBER() gives a unique sequential number, breaking ties arbitrarily. RANK() assigns the same rank to ties and leaves gaps (e.g., 1, 2, 2, 4). DENSE_RANK() also assigns same rank to ties but without gaps (e.g., 1, 2, 2, 3). The article includes a result set image illustrating these differences on student GPA data.

8. Calculating Delta Values

LAG()

and LEAD() access previous or subsequent rows to compute period-over-period changes. Examples show month-over-month sales delta using LAG(sales, 1) and year-over-year using LAG(sales, 12).

9. Calculating Running Totals

Using SUM() OVER (ORDER BY Month) produces a cumulative sum. The example calculates cumulative revenue per month, with an accompanying chart image.

10. DateTime Manipulation

Key functions include EXTRACT, DATE_ADD / DATE_SUB, and DATE_TRUNC. The example finds weather record IDs where the temperature is higher than the previous day using DATEDIFF(a.RecordDate, b.RecordDate) = 1 and a self-join on the Weather table.

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