Chapter 29: SQL and Analytics

SQL is not only a language for defining and modifying data; it is also a powerful tool for analysis and reporting. Its aggregation, windowing, and statistical functions can produce detailed insights directly within the database, reducing the need for external processing. This chapter explores how to use SQL for analytical workloads and integrate it into business intelligence pipelines.

Aggregate and window functions for reporting

Aggregate functions summarize multiple rows into single results. Window functions extend this idea by allowing aggregates over specific partitions of data while still returning individual rows. Together, they form the backbone of reporting queries.

Common aggregate functions

SELECT department, COUNT(*) AS staff, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Aggregates collapse multiple rows into summaries per group. You can add HAVING to filter results after aggregation.

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

Window functions for running totals and rankings

Window functions compute values across a related set of rows, defined by OVER clauses, without collapsing the result set.

SELECT
  department,
  employee_id,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS total_in_dept,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

This calculates per department totals and ranks each employee by salary while still listing individual rows.

Running totals and moving averages

SELECT
  sale_date,
  SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_avg
FROM sales;

These patterns are essential for time series and financial analysis, where each new row builds on previous ones.

Combining aggregates and windows

Both types of functions can appear in the same query, allowing summaries and details together:

SELECT
  region,
  store_id,
  SUM(sales) AS store_total,
  SUM(SUM(sales)) OVER (PARTITION BY region) AS region_total
FROM sales
GROUP BY region, store_id;

Here, the grouped result provides store_total, and the window computes region_total across grouped rows.

Using SQL for data analysis

SQL can perform most classical analytical steps: filtering, aggregation, correlation, and cohort analysis. When datasets fit within the database, these queries can deliver results faster than exporting to external tools.

Descriptive statistics

Basic statistical summaries can be computed directly in SQL:

SELECT
  AVG(amount) AS mean,
  STDDEV(amount) AS stddev,
  MIN(amount) AS min,
  MAX(amount) AS max,
  COUNT(*) AS samples
FROM orders;

Some engines provide VARIANCE(), MEDIAN(), and PERCENTILE_CONT() for more advanced measures.

Cohort and retention analysis

Cohort analysis groups users or transactions by their first event date and measures retention or performance over time.

WITH first_use AS (
  SELECT user_id, MIN(order_date) AS cohort_date
  FROM orders
  GROUP BY user_id
)
SELECT
  f.cohort_date,
  DATE_TRUNC('month', o.order_date) AS active_month,
  COUNT(DISTINCT o.user_id) AS active_users
FROM orders o
JOIN first_use f ON o.user_id = f.user_id
GROUP BY f.cohort_date, active_month
ORDER BY f.cohort_date, active_month;

This pattern reveals how many users return after their initial period. It is widely used in product analytics.

Correlation and outlier detection

SQL:2023 defines COVAR_POP(), COVAR_SAMP(), CORR(), and REGR_SLOPE() to measure relationships between columns.

SELECT
  CORR(height, weight) AS correlation,
  REGR_SLOPE(weight, height) AS slope
FROM measurements;

Outliers can be flagged by comparing values to standard deviation ranges:

SELECT id, amount
FROM sales
WHERE amount > (
  SELECT AVG(amount) + 3 * STDDEV(amount)
  FROM sales
);

Pivoting and unpivoting for summaries

Pivoting turns row data into columns, while unpivoting does the reverse. Syntax varies by engine, but SQL:2023 provides PIVOT and UNPIVOT constructs.

SELECT *
FROM sales
PIVOT (
  SUM(amount) FOR region IN ('EU', 'US', 'APAC')
) AS p;

This produces one row per grouping key and a column per region. Engines without PIVOT can emulate it with conditional aggregation:

SELECT
  product,
  SUM(CASE WHEN region = 'EU' THEN amount END) AS eu_sales,
  SUM(CASE WHEN region = 'US' THEN amount END) AS us_sales,
  SUM(CASE WHEN region = 'APAC' THEN amount END) AS apac_sales
FROM sales
GROUP BY product;

Using SQL for BI integration

Modern business intelligence (BI) tools rely on SQL to extract and visualize insights. Whether connected through live queries or cached extracts, SQL defines the data model for dashboards and analytics layers.

Connecting BI tools

BI platforms such as Power BI, Tableau, Looker, and Metabase connect through standard protocols (ODBC, JDBC, or native drivers). They send SQL queries to the database and render results as charts or tables.

-- Example: a BI tool may issue
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
ORDER BY total_sales DESC;

Views are ideal interfaces for BI tools, encapsulating business logic and simplifying joins. Expose only the necessary columns and apply row level permissions where needed.

Creating analytical views

CREATE VIEW v_sales_summary AS
SELECT
  region,
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS total_sales,
  COUNT(DISTINCT customer_id) AS customers
FROM orders
GROUP BY region, month;

Analytical views provide a stable surface for BI dashboards to query without risk of schema changes breaking charts.

Caching and extracts

Many BI systems store precomputed extracts for performance. Use scheduled jobs or materialized views to populate them efficiently.

CREATE MATERIALIZED VIEW mv_sales_monthly AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS total
FROM orders
GROUP BY month;

Refresh materialized views regularly, or trigger updates based on data change events.

Data modeling for analytics

Adopt a star schema or snowflake schema to make analytical queries simpler and faster. Separate fact tables (transactions) from dimension tables (descriptive attributes).

-- Simplified star schema
FACT_SALES(order_id, date_id, product_id, amount)
DIM_DATE(date_id, date, month, year)
DIM_PRODUCT(product_id, category, brand)

Analysts can then write concise joins for reports:

SELECT
  d.month,
  p.category,
  SUM(f.amount) AS total_sales
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY d.month, p.category;

Automating analytics pipelines

Use stored procedures, scheduled jobs, or external orchestrators (for example Airflow or dbt) to automate analysis refreshes and report generation.

-- Example task scheduling pseudo SQL
CALL refresh_materialized_view('mv_sales_monthly');

By combining SQL’s analytical power with BI integration, you can build self updating dashboards, real time metrics, and consistent data models across your organization.