Reporting and Dashboards

🔄 Quick Recall: In the last lesson, you optimized query performance with EXPLAIN ANALYZE, strategic indexing, and query rewriting. Now let’s use those fast, efficient queries to build reports and dashboards.

From Queries to Business Intelligence

A query answers a question. A report answers the same question consistently, reliably, and in a format stakeholders can act on. AI helps you bridge this gap — turning one-off queries into production-quality reporting systems.

Building Report Queries

Report queries need to be defensive and self-documenting:

Database: PostgreSQL
Tables:
- orders (id, customer_id, total, order_date, status, region)
- customers (id, name, email, segment, created_at)
- products (id, name, category, price)
- order_items (id, order_id, product_id, quantity)

Build a monthly revenue report query that shows:
1. Revenue by month for the last 12 months
2. Month-over-month growth percentage
3. Revenue by region
4. Top 5 product categories by revenue
5. New vs. returning customer revenue split

Requirements:
- Use dynamic dates (no hardcoded values)
- Handle NULL values and zero divisions safely
- Use meaningful column aliases
- Format large numbers for readability
- Include a total/summary row

✅ Quick Check: Why does the prompt specify “handle NULL values and zero divisions safely”?

Because reports that show NULL or crash with “division by zero” destroy stakeholder trust. If a region had no sales last month, the growth calculation divides by zero. NULLIF and COALESCE handle this: ROUND(current_revenue / NULLIF(previous_revenue, 0) * 100 - 100, 1) returns NULL instead of crashing, and COALESCE converts that to a display-friendly value like ‘N/A’ or 0.

Creating Views for Reusable Reports

Wrap complex reports in views:

I've written these reporting queries:
[paste your queries]

Convert them into:
1. Regular views for real-time reports (always show current data)
2. Materialized views for expensive reports (refresh daily)
3. A refresh schedule: which views need daily refresh vs. hourly vs. weekly
4. Grant statements so the 'reporting' role can read them

Include CREATE OR REPLACE so I can update the views without dropping them.

When to use each type:

Time Intelligence Patterns

The hardest part of reporting queries is handling time correctly. Have AI generate these patterns for your database:

Database: PostgreSQL

Generate reusable date expressions for:
1. Current month to date
2. Previous month (full)
3. Same period last year
4. Rolling 7-day, 30-day, 90-day windows
5. Year-to-date
6. Quarter-to-date
7. Week-over-week comparison
8. Monthly series for the last 12 months (including months with zero data)

For #8, show how to use generate_series to fill gaps where there's no data for a month.

The generate_series technique is particularly important. Without it, months with no orders simply don’t appear in the results — creating misleading gaps in charts.

Building Dashboard Queries

Dashboards need multiple coordinated queries. Have AI design the full suite:

I'm building an executive dashboard for an e-commerce business.

Design queries for these dashboard widgets:
1. KPI cards: total revenue (today, MTD, YTD), order count, average order value, active customers
2. Revenue trend: daily revenue for the last 30 days (line chart)
3. Revenue breakdown: by region (pie/bar chart)
4. Top products: top 10 by revenue this month (table)
5. Customer metrics: new signups, retention rate, churn risk
6. Comparison: this month vs. last month for all key metrics

Each query should:
- Return data in a format ready for charting (dates as labels, values as numbers)
- Use dynamic dates
- Execute in under 1 second on a table with 5 million orders
- Include the column format that charting libraries expect (x-axis, y-axis naming)

Automated Report Distribution

For reports that need to go out regularly:

I need to automate a weekly sales report that:
1. Runs every Monday at 8am
2. Queries the database for last week's metrics
3. Formats the results as a summary with highlights
4. Identifies notable changes (>20% increase/decrease vs. prior week)

Generate:
1. The SQL query (parameterized for any date range)
2. A script (Python or bash) to run the query and format results
3. The scheduling command (cron or similar)
4. Error handling (what happens if the database is down)

✅ Quick Check: Why should the automated report highlight “notable changes” rather than just showing raw numbers?

Because stakeholders don’t have time to analyze every number. A report showing “Revenue: $47,200” forces them to remember last week was $39,000 and do the math. A report showing “Revenue: $47,200 (+21% vs. last week)” tells the story immediately. Automated anomaly detection — flagging metrics that changed more than 20% — turns a data dump into an actionable alert.

Exercise: Build a Reporting Suite

Using your own database (or the e-commerce schema from this course):

1. Write 3 report queries (revenue summary, customer metrics, product performance)
2. Convert the most complex one into a view
3. Add time intelligence (month-over-month comparison, rolling averages)
4. Format the output for readability (meaningful aliases, number formatting, null handling)

Key Takeaways

• Report queries need defensive coding: handle nulls (COALESCE), prevent division by zero (NULLIF), use clear aliases
• Use dynamic date functions instead of hardcoded dates so reports stay accurate automatically
• Views encapsulate complex report logic; materialized views pre-compute expensive reports for instant access
• generate_series fills date gaps so months or days with zero data still appear in charts and trends
• Dashboard queries should return chart-ready formats with clear x-axis/y-axis column naming
• Automated reports that highlight notable changes (anomaly detection) are vastly more useful than raw data dumps

Up Next: In the next lesson, you’ll learn database security, backups, and maintenance — protecting your data and keeping your database healthy over time.