SQL for Business Intelligence & Reporting: From Raw Data to Insights

Table of Contents

1. Introduction
2. Role of SQL in Business Intelligence (BI)
3. Key Metrics and KPIs in Reporting
4. Data Preparation for Reporting
5. Filtering and Slicing Data
6. Aggregation and Grouping Techniques
7. Common Analytical Queries
8. Calculating Growth, YoY, MoM Changes
9. Using CASE for Conditional Logic
10. Building Executive Dashboards with SQL
11. Generating Cross-Tab Reports (Pivoting)
12. Time-Series Analysis for BI
13. Funnel and Conversion Analysis
14. Segmentation and Cohort Queries
15. Joining Multiple Data Sources
16. Window Functions for Rankings & Trends
17. Dynamic Date Filtering and Rolling Periods
18. SQL for Visual BI Tools (Power BI, Tableau, etc.)
19. Best Practices for BI SQL Design
20. Summary and What’s Next

1. Introduction

SQL is the foundation of data analysis in business intelligence. Whether you’re building dashboards or generating reports, SQL empowers analysts to extract insights from raw data and convert it into actionable information.

2. Role of SQL in Business Intelligence (BI)

• Extract, transform, and summarize large datasets
• Create reusable queries for dashboards and reporting tools
• Answer business questions with accuracy and speed
• Feed BI tools (Tableau, Power BI, Looker) with clean datasets

3. Key Metrics and KPIs in Reporting

Examples include:

• Revenue, Profit, Gross Margin
• Conversion Rate, Retention Rate
• Average Order Value, Customer Lifetime Value
• DAU, MAU (Daily/Monthly Active Users)
• NPS, Churn Rate

SQL is used to define and calculate all of the above consistently.

4. Data Preparation for Reporting

Create views or CTEs to clean and reshape data:

WITH clean_sales AS (
  SELECT *, DATE(order_date) AS day
  FROM sales
  WHERE status = 'completed'
)
SELECT day, SUM(total_amount) AS daily_sales
FROM clean_sales
GROUP BY day;

5. Filtering and Slicing Data

SELECT category, COUNT(*) AS orders
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY category;

Add filters for geography, channel, segment, etc.

6. Aggregation and Grouping Techniques

Use aggregate functions:

• SUM()
• AVG()
• COUNT()
• MIN(), MAX()

Example:

SELECT region, AVG(order_value) FROM sales GROUP BY region;

7. Common Analytical Queries

• Top N products:

SELECT product_id, SUM(quantity) AS sold
FROM order_items
GROUP BY product_id
ORDER BY sold DESC
LIMIT 10;

• Revenue by customer segment

SELECT segment, SUM(revenue)
FROM customers JOIN orders USING(customer_id)
GROUP BY segment;

8. Calculating Growth, YoY, MoM Changes

WITH sales_by_month AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS mom_percent
FROM sales_by_month;

9. Using CASE for Conditional Logic

SELECT
  CASE
    WHEN total >= 1000 THEN 'High Value'
    WHEN total >= 500 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS order_type,
  COUNT(*) AS count
FROM orders
GROUP BY order_type;

10. Building Executive Dashboards with SQL

Create summary tables/views like:

SELECT
  COUNT(DISTINCT user_id) AS active_users,
  SUM(revenue) AS total_revenue,
  AVG(order_value) AS avg_order_value
FROM daily_metrics
WHERE date = CURRENT_DATE;

Feed this into a dashboard tool via scheduled queries or materialized views.

11. Generating Cross-Tab Reports (Pivoting)

PostgreSQL:

SELECT * FROM crosstab(
  'SELECT region, product, SUM(sales) FROM data GROUP BY 1,2',
  'SELECT DISTINCT product FROM data ORDER BY 1'
) AS final(region TEXT, product_a INT, product_b INT);

MySQL workaround:

SELECT region,
  SUM(CASE WHEN product = 'A' THEN sales ELSE 0 END) AS product_a,
  SUM(CASE WHEN product = 'B' THEN sales ELSE 0 END) AS product_b
FROM data
GROUP BY region;

12. Time-Series Analysis for BI

Use DATE_TRUNC, GROUP BY on time:

SELECT DATE_TRUNC('week', created_at) AS week, COUNT(*) AS signups
FROM users
GROUP BY week
ORDER BY week;

13. Funnel and Conversion Analysis

WITH step1 AS (
  SELECT user_id FROM visits WHERE page = 'landing'
),
step2 AS (
  SELECT user_id FROM visits WHERE page = 'signup'
)
SELECT
  (SELECT COUNT(*) FROM step1) AS landing_visits,
  (SELECT COUNT(*) FROM step2) AS signups,
  ROUND(100.0 * (SELECT COUNT(*) FROM step2) / (SELECT COUNT(*) FROM step1), 2) AS conversion_rate;

14. Segmentation and Cohort Queries

WITH cohort AS (
  SELECT user_id, MIN(signup_date) AS cohort_date
  FROM users
  GROUP BY user_id
),
activity AS (
  SELECT user_id, DATE_TRUNC('month', login_date) AS activity_month
  FROM logins
)
SELECT cohort.cohort_date, activity.activity_month, COUNT(DISTINCT activity.user_id) AS active_users
FROM cohort
JOIN activity USING(user_id)
GROUP BY cohort_date, activity_month;

15. Joining Multiple Data Sources

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN payments p ON o.order_id = p.order_id
WHERE p.status = 'paid';

Essential for consolidated reporting across systems.

16. Window Functions for Rankings & Trends

SELECT product_id, region,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank
FROM regional_sales;

Used for leaderboards, trend tracking, etc.

17. Dynamic Date Filtering and Rolling Periods

SELECT * FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days';

Make filters parameterized in tools like Power BI or Tableau.

18. SQL for Visual BI Tools (Power BI, Tableau, etc.)

• Use views to standardize logic
• Use calculated columns or filters in SQL when possible
• Avoid subqueries and heavy joins in real-time dashboards
• Ensure column names are friendly and consistent
• Feed data via scheduled extract or live connection

19. Best Practices for BI SQL Design

• Modularize logic with CTEs or views
• Create intermediate staging tables for slow aggregations
• Maintain naming conventions (dim_, fact_, agg_)
• Pre-aggregate metrics for performance
• Use indexing wisely on filterable dimensions (e.g., region, created_at)

20. Summary and What’s Next

SQL is a cornerstone of BI and reporting workflows, providing the engine that powers real-time dashboards, quarterly reports, and executive insights. Mastering its capabilities for filtering, aggregating, joining, and transforming data is crucial for any data analyst or BI engineer.