SQL for Business Intelligence
Topic 4 of 12 · In Business Intelligence · September 9, 2026
Master the SQL skills BI professionals use every day: advanced queries, window functions, CTEs, pivoting, aggregation strategies and performance tuning.
Overview
SQL is the language of data — and for BI professionals it is the single most important technical skill. Every data warehouse is queried with SQL; every ETL transformation is expressed in SQL; every Power BI data model is ultimately backed by SQL queries. If you can write clear, efficient SQL, you can answer any business question that data can answer.
This topic goes beyond basic SELECT and JOIN. You will learn the advanced SQL patterns that BI professionals use daily: window functions for running totals and rankings, CTEs for readable multi-step analysis, pivoting for cross-tab reports, aggregation strategies for KPIs, and performance tuning to keep queries fast on billion-row tables. By the end, you will be able to write the SQL behind any dashboard or report.
Before You Start
- Load fact_sales, dim_date, dim_store, dim_product and dim_customer from the warehousing mini project into your SQL environment (SQLite locally or DB Fiddle)
- Populate fact_sales with at least 200,000 rows so performance answers are realistic rather than toy-scale
- Open the topic's window-functions and cte-chain examples side by side to compare against your own attempts
- Be able to write a GROUP BY and an INNER JOIN without reference material before you start
- Write out the KPI list from the fundamentals topic (revenue, profit margin, orders, average order value) to use as target queries
Learning Objectives
- Write complex multi-table JOIN queries with confidence
- Use window functions for rankings, running totals, moving averages and lead/lag
- Structure analysis with CTEs (Common Table Expressions) for readability
- Pivot and unpivot data for cross-tabular reports
- Apply correct aggregation strategies for KPI calculations
- Tune queries for performance on large warehouse tables
- Understand materialized views and when to use them
- Write SQL that Power BI and other BI tools can consume efficiently
Prerequisites
Continue from Data Warehousing Concepts for the schema context. Basic SQL (SELECT, WHERE, GROUP BY, JOIN) is assumed — if you need a refresher, review a SQL basics course first.
Joins in Practice
BI queries almost always join fact and dimension tables. The three join types you will use most:
| Join Type | What It Does | BI Use Case |
|---|---|---|
| INNER JOIN | Returns only matching rows from both tables | Fact + dimension where all facts have valid dimension keys |
| LEFT JOIN | Returns all rows from the left table, matches from the right (NULLs if no match) | Fact + dimension where some facts may have missing dimension data |
| FULL OUTER JOIN | Returns all rows from both tables | Reconciliation: comparing two data sources for missing records |
Tip: In a properly designed star schema, you should rarely need FULL OUTER JOIN. If you are using it frequently, revisit your schema design.
-- Revenue by product category (fact + dimension join)
SELECT
p.category,
SUM(f.revenue) AS total_revenue,
SUM(f.quantity) AS total_quantity
FROM fact_sales f
INNER JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category
ORDER BY total_revenue DESC;Window Functions
Window functions perform calculations across a set of rows related to the current row, without collapsing the result into a single row like GROUP BY does. They are the most powerful tool in a BI SQL developer's kit.
Key patterns:
- Ranking:
RANK(),DENSE_RANK(),ROW_NUMBER()— who is top 10? What is each product's rank in revenue? - Running totals:
SUM(...) OVER (ORDER BY date)— cumulative revenue over time - Moving averages:
AVG(...) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)— 7-day rolling average - Lead / Lag:
LAG(revenue, 1)— previous month's revenue for period-over-period comparison - Percent of total:
revenue * 100.0 / SUM(revenue) OVER ()— each category's share
-- Revenue rank per region + month-over-month growth
WITH monthly_revenue AS (
SELECT
d.year_month,
g.region,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_store g ON f.store_key = g.store_key
GROUP BY d.year_month, g.region
)
SELECT
year_month,
region,
revenue,
RANK() OVER (PARTITION BY year_month ORDER BY revenue DESC) AS region_rank,
LAG(revenue, 1) OVER (PARTITION BY region ORDER BY year_month) AS prev_month,
ROUND(
(revenue - LAG(revenue, 1) OVER (PARTITION BY region ORDER BY year_month))
* 100.0 / NULLIF(LAG(revenue, 1) OVER (PARTITION BY region ORDER BY year_month), 0),
1
) AS mom_growth_pct
FROM monthly_revenue
ORDER BY year_month, region_rank;CTEs: Writing Readable Analysis
Common Table Expressions (CTEs) — the WITH clause — let you break complex queries into named, sequential steps. Each CTE is a temporary result set that subsequent CTEs or the final SELECT can reference.
Why CTEs matter for BI:
- Readability — name each step (raw_sales, monthly_totals, ranked) so a colleague can follow your logic
- Reuse — reference the same CTE multiple times in the final query without duplicating code
- Debugging — comment out the final SELECT and run each CTE independently to check intermediate results
- Complexity management — multi-step KPI calculations (e.g. cohort analysis, funnel analysis) become manageable
-- CTE chain: a complete KPI query in readable steps
WITH
-- Step 1: Clean and join raw data
clean_sales AS (
SELECT f.*, p.category, d.year_month
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE f.revenue > 0
),
-- Step 2: Aggregate by category and month
monthly AS (
SELECT
category,
year_month,
SUM(revenue) AS revenue,
COUNT(*) AS order_count
FROM clean_sales
GROUP BY category, year_month
),
-- Step 3: Add month-over-month growth
with_growth AS (
SELECT
*,
LAG(revenue) OVER (PARTITION BY category ORDER BY year_month) AS prev_revenue
FROM monthly
)
-- Step 4: Final output
SELECT
category,
year_month,
revenue,
order_count,
ROUND((revenue - prev_revenue) * 100.0 / NULLIF(prev_revenue, 0), 1) AS growth_pct
FROM with_growth
ORDER BY category, year_month;Pivoting and Unpivoting
Pivoting transforms rows into columns — turning a "long" table into a "wide" cross-tab report. This is common for monthly revenue reports where each month becomes a column.
Unpivoting does the reverse: wide tables become long ones (useful when a source provides data in a format that does not match your warehouse schema).
-- Pivot: monthly revenue as columns
SELECT category, [2025-01], [2025-02], [2025-03]
FROM (
SELECT category, year_month, revenue
FROM monthly_sales
) AS source
PIVOT (
SUM(revenue)
FOR year_month IN ([2025-01], [2025-02], [2025-03])
) AS pivot_table;
-- Unpivot: wide to long
SELECT category, month, revenue
FROM wide_table
UNPIVOT (
revenue FOR month IN ([2025-01], [2025-02], [2025-03])
) AS unpivot_table;Aggregation Strategies for KPIs
BI KPIs often require non-obvious aggregation logic. Common patterns:
| KPI | SQL Pattern | Pitfall |
|---|---|---|
| Average Order Value | SUM(revenue) / COUNT(DISTINCT order_id) | Do not use AVG(line_revenue) — it averages line items, not orders |
| Conversion Rate | SUM(CASE WHEN converted THEN 1 ELSE 0 END) * 100.0 / COUNT(*) | Requires a consistent numerator/denominator definition |
| Retention Rate | Cohort-based: customers active in month N / customers active in month 0 | Define the cohort window carefully |
| Median | PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) | Not available in all SQL dialects; MySQL lacks native percentile |
| Distinct Count | COUNT(DISTINCT customer_id) | Expensive on large tables; consider approximate count (HyperLogLog) |
Performance Tuning for BI Queries
BI queries run against large tables and must be fast enough for interactive dashboards. Key optimization strategies:
- Filter early — add WHERE clauses to reduce rows before joins and aggregations
- Use indexed columns — join and filter on indexed columns (date_key, surrogate keys)
- Avoid SELECT * — only select the columns you need
- Pre-aggregate — create summary tables or materialized views for common KPIs
- Minimize DISTINCT — it forces sorting/hashing; use approximate functions when possible
- Partition large tables — by date, so queries scan only relevant partitions
- Use EXPLAIN / execution plans — understand what the query engine actually does
Hands-On Practice
Use a SQL practice environment: SQLite locally, a free PostgreSQL instance (e.g. Supabase, ElephantSQL), or an online SQL editor like DB Fiddle.
- Write a query that joins a fact table with 3 dimension tables and calculates total revenue by category, region and month.
- Add a window function: rank regions by revenue within each month, and calculate month-over-month growth percentage.
- Rewrite the same query using CTEs with clear step names (raw_data, monthly_totals, ranked).
- Write a pivot query that shows revenue by category for each quarter as columns.
- Calculate three KPIs: average order value, distinct customer count, and revenue percent of total — all in one query.
- Run EXPLAIN on your most complex query and identify at least one optimization opportunity.
Deeper Worked Examples
Month-over-month revenue by region with rankings
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
KPI set for a subscription SaaS business
Step 1
Step 2
Step 3
Step 4
Step 5
Step 6
Common Mistakes
- Using AVG() on a line-item level to compute average order value — always aggregate to the correct grain first
- Writing deeply nested subqueries instead of CTEs — CTEs are far more readable and debuggable
- Using SELECT * in production queries — fetch only the columns you need
- Forgetting NULLIF in division — division by zero crashes queries or returns incorrect results
- Pivoting in SQL when the BI tool can do it — keep warehouse data in long format
- Ignoring NULL behavior in aggregations — SUM ignores NULLs, COUNT(*) includes them, COUNT(col) does not
- Not testing queries against production-scale data — 1,000 rows and 1 billion rows behave very differently
Real-World Usage
A BI analyst at a SaaS company writes SQL every day: pulling MRR trends, building cohort retention analyses, creating pipeline reports for the sales team. The queries must be fast (executed live during dashboard interactions), correct (a wrong aggregation means a wrong KPI on the CEO's screen) and maintainable (a colleague must understand them in six months). The window functions, CTE patterns and performance techniques in this topic are exactly what you will use in production.
Mini Project
Business problem: A subscription-based SaaS company needs a set of SQL queries that power their executive dashboard. The warehouse has fact_subscriptions (subscription_key, customer_key, plan_key, start_date, end_date, mrr), dim_customer (name, company, segment, signup_date) and dim_plan (name, tier, price).
Objective: write 6 production-quality SQL queries that answer the most important SaaS business questions.
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
| Deliverable | What It Proves |
|---|---|
| 6 production SQL queries | You can write BI-grade SQL |
| CTEs used throughout | You write readable, maintainable queries |
| Window functions in 3+ queries | You can rank, compare and trend data |
| Correct KPI logic (no AVG mistakes) | You understand aggregation grain |
| All queries run in <2 seconds on sample data | You consider performance |
Key Takeaways
- JOINs: INNER for clean star schemas, LEFT for incomplete data, FULL OUTER for reconciliation
- Window functions: RANK, SUM OVER, LAG/LEAD, moving averages — analytics without collapsing rows
- CTEs make complex queries readable, debuggable and maintainable
- Pivot/unpivot: rows ↔ columns; prefer long data in the warehouse, pivot in the BI tool
- KPI aggregation: always aggregate to the correct grain before averaging or counting
- Performance: filter early, use indexes, avoid SELECT *, pre-aggregate, partition
- Materialized views pre-compute common aggregations for fast dashboard queries
Key Terms & Glossary
| Term | Meaning |
|---|---|
| Window function | A function such as RANK() or SUM(...) OVER (...) that computes across a set of rows related to the current row without collapsing the result set like GROUP BY. |
| Common Table Expression | The WITH clause that names a temporary result set, breaking complex multi-step queries into readable, independently debuggable steps. |
| Pivot / unpivot | Reshaping rows into columns or columns into rows; pivoting builds cross-tab reports while unpivoting returns wide data to long format. |
| Materialised view | A pre-computed and stored query result that turns a repeated slow aggregation into a sub-second read on large warehouse tables. |
| EXPLAIN | A command that returns the query plan, showing the scans, joins and indexes the engine will use, so a slow query can be diagnosed. |
| NULLIF | A SQL function that returns NULL when its two arguments are equal, commonly used to protect division denominators such as LAG(revenue, 1) from division by zero. |
Learning Resources
Mode Analytics — SQL Tutorial for Data Analysis
A comprehensive, practical SQL tutorial covering window functions, CTEs and analysis patterns.
Microsoft Learn — Window Functions (Transact-SQL)
Official documentation for SQL Server window functions with syntax and examples.
Coursera — Advanced SQL for Data Scientists
University of Michigan course covering CTEs, window functions and query optimization.
DB Fiddle
Free online SQL editor to test queries against MySQL, PostgreSQL and SQLite.
Luke Barousse — SQL for BI (YouTube)
Practical SQL tutorials focused on BI and analytics use cases.
Use The Index, Luke
An interactive guide to SQL performance and indexing — essential for BI query tuning.
Chapter Quiz
Chapter Quiz — SQL for Business Intelligence
The SQL that powers every dashboard: correct grain, windows, CTEs, JOIN choices and keeping billion-row queries fast.
Question 1 of 5
A report computes average order value as AVG(line_revenue) from a line-item table. What is wrong?
Knowledge Check
End-of-Topic Exam
Hint: The performance section and its materialised view callout list the main levers.
Model answer: Likely causes are scanning the whole table, joining on unindexed columns, selecting every column, expensive DISTINCT and aggregation performed at query time. In priority order: first filter early with WHERE on indexed columns such as date_key to reduce rows before joins and aggregations; second inspect the plan with EXPLAIN and add indexes or partition the table by date; third drop SELECT * and remove unnecessary DISTINCT calls; fourth pre-aggregate the repeated aggregation into a materialised view. For an interactive dashboard the target should be well under two seconds.
Hint: Aggregate to daily revenue first, then apply AVG OVER with a ROWS BETWEEN frame; the career topic borrows this question.
Model answer: First aggregate to a daily CTE: WITH daily AS (SELECT d.full_date AS full_date, SUM(f.revenue) AS revenue FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key GROUP BY d.full_date). Then select AVG(revenue) OVER (ORDER BY full_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d from daily. The frame covers the current day plus the six previous days; during the first six days of the series the frame contains fewer rows, so the average is computed over whatever exists, which must be accepted or explicitly excluded by the business.
Hint: The aggregation-strategies table and the topic's mistakes list hinge on this error.
Model answer: AVG(line_revenue) averages line items rather than orders, so a five-line order counts five times while a single-line order counts once, and the result is not the true average value per order. The correct pattern is SUM(revenue) / COUNT(DISTINCT order_id), always aggregating to the order grain before dividing. This is the classic aggregate-to-the-correct-grain-before-averaging error.