Advanced SQL Techniques

Level Up Your Queries

When you need to perform calculations that go beyond simple aggregates, window functions are your best friend. Unlike a typical aggregate function that collapses rows into a single output, a window function performs a calculation across a set of table rows that are related to the current row. This means you can compute running totals, rankings, or moving averages without losing the individual rows from your result set.

The magic happens with the OVER() clause, which defines the “window” of rows the function operates on. Within this clause, PARTITION BY divides the rows into groups (like departments or regions), and ORDER BY sets the order of rows within each partition.

Ranking Functions

Let's look at a common interview task: ranking items within categories. Imagine you have a table of employees and you need to rank them by salary within each department. There are a few ways to do this, each with a subtle difference.

ROW_NUMBER() assigns a unique, sequential integer to each row within its partition. No two rows will have the same number, even if their values are identical. It's great for simply numbering rows.

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;

But what if you have ties? That's where RANK() and DENSE_RANK() come in. RANK() assigns the same rank to rows with the same value, but it skips the next rank(s) to account for the tie. If two employees are tied for 2nd place, the next rank will be 4.

DENSE_RANK() also assigns the same rank to ties, but it doesn't leave gaps. After a tie for 2nd place, the next rank is 3. This is often more useful when you want a continuous sequence of ranks.

SELECT
  employee_name,
  department,
  salary,
  RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

Key takeaway: Use RANK() when you want to see gaps after ties, reflecting how many people are ahead. Use DENSE_RANK() for a simple, gap-free ranking of distinct values.

Queries Within Queries

Subqueries, or inner queries, are a fundamental tool for solving multi-step problems. They let you perform a query whose results are used by another, outer query. They come in two main flavors: non-correlated and correlated.

A non-correlated subquery is the simpler type. It’s a self-contained query that runs first and only once. Its result is then fed to the outer query. For example, to find all employees who earn more than the company-wide average salary:

-- The inner query runs once to find the average salary.
SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

A correlated subquery is more complex. The inner query depends on the outer query for its values, meaning it runs once for every row processed by the outer query. This can be powerful, but often inefficient.

Here's how you might find the employees who earn the highest salary in their respective departments using a correlated subquery. The inner query recalculates the max salary for the department of the current employee being evaluated by the outer query.

SELECT employee_name, salary, department
FROM employees e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

Simplifying with CTEs

While subqueries are useful, they can quickly make your code hard to read and debug, especially when they're nested several layers deep. Common Table Expressions, or CTEs, are here to help.

A CTE lets you define a temporary, named result set that you can reference within your main query. You define it using the WITH keyword. They break complex logic into clean, logical steps, making your code much more maintainable.

CTEs (Common Table Expressions) aren’t exactly a beginner feature — but once you start using them, they change the way you write SQL.

Let's rewrite the previous correlated subquery example using a CTE and a window function. We'll first create a temporary result set that includes the salary rank for each employee within their department, then select only those with a rank of 1.

WITH RankedSalaries AS (
  SELECT
    employee_name,
    salary,
    department,
    RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rank
  FROM employees
)
SELECT
  employee_name,
  salary,
  department
FROM RankedSalaries
WHERE rank = 1;

Notice how much easier that is to follow? The logic is separated into a clear, preparatory step (RankedSalaries) and a final selection step. This approach is not only more readable but often more performant than a correlated subquery.

Quiz Questions 1/6

What is the primary difference between a window function and a standard aggregate function like SUM() or AVG()?

Quiz Questions 2/6

Consider a table of students' scores where two students are tied for 2nd place. Which ranking function would assign the next rank as 3?

Mastering these techniques will significantly improve the clarity, efficiency, and power of your SQL queries.

Mastering Google SQL Interviews