Chapter 12: SQL for Data Analysis

This chapter covers techniques for analyzing data with SQL, including window functions and common table expressions (CTEs).

Analyzing data with SQL

SQL has a wide set of features for data analysis that let you aggregate, filter, and manipulate datasets to draw conclusions from them. Whether you’re working with transactional data, log files, or sensor readings, SQL can help you find the patterns, trends, and anomalies in your data.

Aggregation functions

SQL’s built-in aggregation functions, such as SUM, AVG, COUNT, MIN, and MAX, summarize data across rows or groups of rows. You can use them to calculate metrics such as total sales, average revenue, or the number of transactions in a given time period.

-- Calculate total sales for each product category
SELECT category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY category;

Filtering data

The WHERE clause filters rows on specific criteria, such as dates, categories, or numerical ranges. Filters let you focus your analysis on the relevant subsets of data and leave out outliers or irrelevant observations.

-- Filter transactions for the month of January 2024
SELECT *
FROM transactions
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31';

Join operations

JOIN operations combine data from multiple tables based on common keys or relationships. You need joins to bring together data from different sources or tables, which lets you do cross-sectional analysis and see your data as a whole.

-- Combine sales data with product information
SELECT s.*, p.product_name
FROM sales s
JOIN products p ON s.product_id = p.product_id;

Window functions

Window functions perform calculations across a set of rows related to the current row. They let you calculate aggregated values, rankings, and moving averages without complex subqueries or self-joins.

Example of window functions

With window functions you can:

• Calculate cumulative totals or running sums over a time series or other ordered dataset.
• Rank rows by specific criteria, such as sales performance or customer satisfaction scores.
• Compute moving (rolling) averages to smooth out fluctuations in your data.

-- Calculate cumulative sales over time
SELECT transaction_date, sales_amount,
       SUM(sales_amount) OVER (ORDER BY transaction_date) AS cumulative_sales
FROM sales;

Common table expressions (CTEs)

A common table expression (CTE) defines a temporary result set within a SQL query, which makes complex queries easier to read and maintain. CTEs are particularly useful for breaking a complex analysis into smaller, more manageable steps.

Advantages of CTEs

• Breaking complex logic into smaller, named components makes a query easier to read.
• A common subquery defined once can be referenced several times within a single query, so you can reuse code.
• A recursive CTE (WITH RECURSIVE) can follow hierarchical data, such as an organizational chart, to any depth, which a plain query can’t do.

A CTE is mainly about readability. On its own it doesn’t make a query run faster than the equivalent subquery.

Example of CTEs in data analysis

CTEs work well for:

• Recursive queries that traverse hierarchical data, such as organizational charts or product category hierarchies.
• Preprocessing steps that transform or filter data before further analysis.
• Multi-step analysis workflows involving several queries or data transformations.

Take an employees table where each row names its manager. John has no manager, so he’s at the top:

A recursive CTE works out how far down the chart each employee sits. The first SELECT finds the top of the chart, and the second keeps adding the employees who report to someone already found:

-- Calculate hierarchical structure of employees
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY employee_id;

With aggregation functions, window functions, and common table expressions, you can do a wide range of data analysis directly in your database, from exploring trends in sales data to identifying outliers in financial transactions or running cohort analysis on user behavior.