Complex SQL Queries

Beyond Simple Lookups

You already know how to pull data, filter it, and join tables. That's the foundation. Now, we'll build on it to perform complex analysis directly in the database, answering questions that would otherwise require exporting data to another tool like Python or Excel.

Think of it as the difference between asking for a list of employees and asking for a ranked list of the top three earners in each department, along with their cumulative salary contribution. We'll use tools like window functions and common table expressions to make these sophisticated queries clean and efficient.

Analyzing with Window Functions

Aggregate functions like SUM() or COUNT() are powerful, but they have a major limitation: they collapse multiple rows into a single summary row. When you use GROUP BY, you lose the detail of the individual rows.

solve this. They perform calculations across a set of rows related to the current row—a 'window' of data—but they don't collapse anything. Each row maintains its identity and gets a new value based on the function's calculation.

Let's see them in action. A common task is ranking results. Imagine you want to rank employees by salary within each department.

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

Notice the difference between RANK() and ROW_NUMBER(). If two employees have the same salary, RANK() will give them the same rank and then skip the next number (e.g., 1, 2, 2, 4). ROW_NUMBER(), on the other hand, assigns a unique, sequential number to every row, regardless of ties.

Window functions are also perfect for calculating running totals or moving averages. Here’s how you could calculate cumulative sales over time:

SELECT
  sale_month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY sale_month) AS cumulative_revenue
FROM
  monthly_sales;

Here, the window for each row includes itself and all previous rows, thanks to the ORDER BY clause. This allows SUM() to accumulate the revenue month by month.

Taming Complexity with CTEs

As your queries grow, they can become a tangled mess of nested subqueries. Reading, debugging, and maintaining them feels like untangling knotted headphones. The solution is the WITH clause, which lets you define one or more (CTEs).

A CTE creates a temporary, named result set that you can reference later in your main query. It breaks a complex problem down into logical, sequential steps, making your SQL dramatically easier to understand.

Let's say we want to find all departments where the average salary is above the company-wide average. Using a subquery, it looks like this:

-- With a subquery (harder to read)
SELECT
  department,
  avg_dept_salary
FROM
  (
    SELECT
      department,
      AVG(salary) as avg_dept_salary
    FROM
      employees
    GROUP BY
      department
  ) AS department_salaries
WHERE
  avg_dept_salary > (SELECT AVG(salary) FROM employees);

Now, here is the same logic using CTEs. Notice how each step is clearly defined and named.

-- With CTEs (much clearer)
WITH department_salaries AS (
  SELECT
    department,
    AVG(salary) AS avg_dept_salary
  FROM
    employees
  GROUP BY
    department
),
company_average AS (
  SELECT AVG(salary) AS avg_company_salary FROM employees
)
SELECT
  department,
  avg_dept_salary
FROM
  department_salaries
WHERE
  avg_dept_salary > (SELECT avg_company_salary FROM company_average);

While CTEs don't always improve performance over subqueries (the database optimizer often treats them identically), their value in readability and maintainability is enormous. Clean, logical steps make your code easier for you and others to work with.

Advanced Joins and Logic

Sometimes you need to join a table to itself. This is called a SELF JOIN, and it's essential for querying hierarchical data stored in a flat table. A classic example is an employees table where each employee record contains a manager_id that points to another employee in the same table.

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

Here, 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 connect the manager_id of one row to the employee_id of another.

A more unusual but powerful join is the CROSS JOIN. It produces a Cartesian product, matching every row from the first table with every row from the second. While this can create massive result sets and should be used with caution, it's useful for generating all possible combinations of items, like creating a master list of all available shirt sizes and colors.

Finally, you can inject sophisticated logic into your queries with a CASE statement. This is effectively an if-then-else structure inside your SQL. It allows you to create new categories or transform data on the fly.

For example, you could segment customers into tiers based on their lifetime spending:

SELECT
  customer_id,
  total_spent,
  CASE
    WHEN total_spent > 5000 THEN 'Platinum'
    WHEN total_spent > 1000 THEN 'Gold'
    WHEN total_spent > 100 THEN 'Silver'
    ELSE 'Bronze'
  END AS customer_tier
FROM
  customer_summary;

Combining these techniques, you can build powerful, analytical queries that transform raw data into valuable insights right at the source.

Quiz Questions 1/6

What is the primary advantage of a window function over a standard aggregate function like COUNT() with a GROUP BY clause?

Quiz Questions 2/6

You are ranking employees by salary. Two employees have the exact same salary and are tied for 3rd place. If you use the RANK() function, what rank will be assigned to the employee with the next highest salary?

Data Analyst Professional Path