SQL for Reporting

SQL is the language that connects you to the data. While Excel and Power BI handle presentation, SQL handles extraction and preparation — and for many reports, the query is the report. A well-written SQL query can produce a perfectly formatted, aggregated, time-compared dataset that loads directly into a report or dashboard with zero manual steps.

This topic focuses on report-oriented SQL — not general database programming, but the specific patterns analysts use to produce reporting data. You will learn complex aggregations, window functions for running totals and period comparisons, pivoting data for cross-tab reports, CTEs for readable multi-step logic, creating views that serve as report data layers, and the performance considerations that keep large-report queries fast.

Complete Advanced Excel for Reporting. You should be comfortable with basic SQL: SELECT, WHERE, JOIN, GROUP BY, ORDER BY, and aggregate functions (SUM, COUNT, AVG). If you need a SQL refresher, see the Data Analysis path SQL topic.

A report-oriented query produces a result set that is ready for presentation — already aggregated, formatted, and organized the way the report needs it. Key patterns:

• Multi-level aggregation — aggregate by multiple dimensions (region, then product, then month) with subtotals
• Period comparison — compare this period to last period or to the same period last year
• Percentage of total — each row shows its share of the whole
• Ranking — assign a rank within each group (top N products per region)
• Cumulative / running total — sum accumulating over time
• Gap identification — find missing periods, inactive customers, or unmet thresholds

The goal is to return a result set that needs minimal post-processing. If the report tool (Power BI, Excel, SSRS) can display the query output directly, the report is faster to build and less error-prone.

Window functions perform calculations across a set of rows related to the current row — without collapsing them like GROUP BY. They are the most powerful tool in a report-builder's SQL toolkit.

Core window functions:

These are among the most common report requirements:

Running total:

Reports almost always compare current performance to a baseline — last month, last quarter, last year, or a target. SQL techniques:

Reports often need data in a wide (cross-tab) format: rows for products, columns for months. SQL provides two approaches:

Conditional aggregation (works everywhere):

Common Table Expressions (CTEs) let you break a complex query into named, readable steps — like building a report in stages. Each CTE is a temporary result set that the next CTE (or the final SELECT) can reference.

A SQL view is a named query stored in the database. Views serve as a clean, reusable data layer between raw tables and your reports.

Benefits for reporting:

• Simplification — a view can join, filter and aggregate multiple tables into one readable result set
• Consistency — every report that uses the same view gets the same logic
• Security — expose only the columns and rows a report needs (row-level security via views)
• Maintainability — change the view logic once, all reports using it update automatically

Large reports can take minutes to run if the query is not optimized. Key techniques:

• Select only the columns you need — SELECT * pulls unnecessary data and slows the query
• Filter early — use WHERE clauses to reduce the dataset before aggregation
• Use indexed columns in JOIN and WHERE clauses — check the execution plan for table scans
• Avoid correlated subqueries — they execute once per row. Replace with JOINs or CTEs
• Use EXPLAIN / Execution Plan — understand how the database engine processes your query
• Materialize summary tables — for very large datasets, pre-aggregate into summary tables on a schedule
• Partition large tables — by date or region, so queries scan only relevant partitions

When the query output goes directly to a report, formatting in SQL saves time downstream:

• Date formatting — FORMAT(order_date, 'MMM DD, YYYY') (SQL Server) or TO_CHAR(order_date, 'Mon DD, YYYY') (PostgreSQL/Oracle)
• Null handling — COALESCE(sales, 0) to replace NULLs with zeros for display
• Rounding — ROUND(revenue, 2) for currency, ROUND(pct, 1) for percentages
• Case statements for categories — CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ... END
• Concatenation — CONCAT(first_name, ' ', last_name) for full names
• Conditional totals — SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END)

Use any SQL-accessible database (MySQL, PostgreSQL, SQL Server, or a cloud sandbox like BigQuery free tier):

1. Write a query that produces monthly revenue with month-over-month change and YoY comparison using LAG.
2. Create a running total of daily revenue using SUM() OVER.
3. Pivot a sales table into a cross-tab showing revenue by product across months using conditional aggregation.
4. Build a CTE pipeline: aggregate raw orders → calculate growth → rank regions → output the top 3 regions per quarter.
5. Create a view that joins orders, products, and customers into a report-ready dataset.
6. Analyze a slow query using EXPLAIN — identify the bottleneck and optimize it.

In a mature data organization, report queries are stored in a version-controlled repository (Git), peer-reviewed before deployment, and registered in a data catalog. The analyst writes the query, a senior engineer reviews it for performance and correctness, and the result is published as a view or a materialized table that the BI tool connects to. This separation of concerns means the report builder does not need to understand the entire data model — they only need to know the report view's columns and definitions.

Business problem: A subscription SaaS company needs a quarterly business review (QBR) report. The raw data is in three tables: customers, subscriptions, and invoices.

Objective: build the complete SQL pipeline that produces a QBR-ready dataset.