Complex Data Manipulation

Ranking and Windowing

You already know how to sort data with ORDER BY. But what if you need to rank items, like top-selling products or highest-performing employees? Simple sorting isn't enough when ties occur. You might want to assign the same rank to tied values or give each a unique number. This is where window functions come in.

Window functions perform calculations across a set of table rows that are somehow related to the current row. Think of it as looking through a 'window' at a specific subset of your data to compute a value. The OVER() clause is what defines this window.

Let's start with three key ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK(). They all assign a number to each row within a partition of a result set, but they handle ties differently.

SELECT
  student_name,
  score,
  ROW_NUMBER() OVER(ORDER BY score DESC) AS row_num,
  RANK() OVER(ORDER BY score DESC) AS rank_val,
  DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank_val
FROM exam_scores;

Imagine running this query on a table of student scores. Here's how the results would differ:

student_name	score	row_num	rank_val	dense_rank_val
Alice	95	1	1	1
Bob	92	2	2	2
Charlie	92	3	2	2
Diana	88	4	4	3

Notice the differences:

ROW_NUMBER(): Assigns a unique, sequential number to every row, regardless of ties.
RANK(): Gives tied rows the same rank, but then skips the next rank(s). Charlie and Bob both get rank 2, so the next rank is 4.
DENSE_RANK(): Also gives tied rows the same rank, but it doesn't skip any ranks. After the tie at rank 2, the next rank is 3.

Looking Across Rows

Window functions aren't just for ranking. They can also access data from other rows, which is incredibly useful for time-series analysis. The LAG() and LEAD() functions let you pull values from preceding or succeeding rows, respectively.

This allows you to easily calculate things like month-over-month growth without joining a table to itself. Let's look at monthly sales data.

SELECT
  sale_month,
  monthly_sales,
  LAG(monthly_sales, 1) OVER (ORDER BY sale_month) AS previous_month_sales,
  LEAD(monthly_sales, 1) OVER (ORDER BY sale_month) AS next_month_sales
FROM monthly_reports;

The LAG() function in this query fetches the monthly_sales value from the previous row (ordered by sale_month). LEAD() does the opposite, grabbing the value from the next row.

You can also create running totals or moving averages. The key is to define the window correctly within the OVER() clause. By adding PARTITION BY, you can restart the calculation for different groups.

SELECT
  department,
  employee_name,
  salary,
  -- Running total of salaries within each department
  SUM(salary) OVER (PARTITION BY department ORDER BY employee_name) AS running_total
FROM employees;

Here, PARTITION BY department tells the database to calculate the running total separately for each department. The sum resets every time the department changes.

Joins and Readability

Sometimes you need to join a table to itself. This is called a self-join. It's a common pattern for querying hierarchical data, like an organizational chart stored in a single employees table where a manager_id column points back to another employee's id.

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM
    employees e
LEFT JOIN
    employees m ON e.manager_id = m.employee_id;

In this query, we treat the employees table as two separate tables by giving it two different aliases, e (for employee) and m (for manager). This allows us to link rows within the same table.

As queries become more complex with multiple joins and subqueries, they can become hard to read. Common Table Expressions (CTEs) help solve this by letting you define temporary, named result sets. You create them using the WITH clause.

CTEs break down complex logic into logical, readable steps. They're often easier to debug and maintain than deeply nested subqueries.

-- Using a CTE
WITH DepartmentSales AS (
  SELECT
    d.department_name,
    SUM(s.amount) as total_sales
  FROM sales s
  JOIN employees e ON s.employee_id = e.employee_id
  JOIN departments d ON e.department_id = d.department_id
  GROUP BY d.department_name
)
SELECT
  department_name,
  total_sales
FROM DepartmentSales
WHERE total_sales > 100000;

The logic inside the WITH clause could have been a subquery in the FROM clause, but this version is much cleaner. You define the DepartmentSales block first, then query it like a regular table.

Making Queries Fast

Writing a query that returns the correct result is only half the battle. You also need to ensure it runs efficiently, especially on large datasets. This is the art of query optimization.

Databases use a query optimizer to create an execution plan, which is a sequence of steps to retrieve the data. You can inspect this plan to understand how your query is actually being run. The command to view it varies by database (e.g., EXPLAIN in PostgreSQL and MySQL, EXPLAIN PLAN FOR in Oracle).

An execution plan might reveal that the database is doing a full table scan, meaning it's reading every single row in a table to find the ones it needs. This is often slow. The primary tool to avoid this is an index.

An index is a separate data structure that stores a copy of one or more columns in a sorted order, allowing the database to find rows much faster, similar to using the index in the back of a book. Creating an index on columns used frequently in WHERE clauses or JOIN conditions is a common optimization strategy.

For example, if you often filter sales data by sale_date and product_id, a composite index on both columns can dramatically speed up queries.

CREATE INDEX idx_sales_product_date
ON sales (product_id, sale_date);

Understanding these advanced techniques will help you write SQL that is not just correct, but also robust, readable, and efficient.

Data Analyst Interview Mastery