Financial KPIs and Executive Dashboards

Executive dashboards distil months of transaction data into a handful of numbers that tell the business story at a glance. This lesson builds the SQL behind a complete financial KPI dashboard — the kind that the CFO, VP Sales, and board review each month.

These queries combine aggregation, period-over-period comparison, and ranking into compact, high-signal outputs.

The Executive Dashboard Framework

A good financial dashboard answers five questions:

1. How much did we bill? (Revenue pipeline)
2. How much did we collect? (Cash health)
3. Who are our best clients? (Revenue concentration)
4. How are we trending? (MoM/YoY growth)
5. Where is the risk? (Overdue exposure)

Each query below maps to one of these questions.

Question 1: Monthly Revenue and Collection Summary

This single query produces the top-line metrics for a month-end finance review. It runs across all time periods so you can see trends, and includes both billed and collected figures.

WITH monthly_summary AS (
    SELECT
        DATE_TRUNC('month', i.issue_date) AS month,
        COUNT(i.invoice_id) AS invoices_issued,
        COUNT(DISTINCT i.client_id) AS active_clients,
        ROUND(SUM((SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id)), 2) AS total_billed,
        ROUND(SUM(CASE WHEN i.status = 'paid' THEN (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) ELSE 0 END), 2) AS total_collected,
        ROUND(SUM(CASE WHEN i.status IN ('overdue', 'sent') THEN (SELECT SUM(ii.quantity * ii.unit_price) FROM invoice_items ii WHERE ii.invoice_id = i.invoice_id) ELSE 0 END), 2) AS outstanding
    FROM invoices i
    WHERE i.status != 'cancelled'
    GROUP BY DATE_TRUNC('month', i.issue_date)
),
with_mom AS (
    SELECT *,
        LAG(total_billed) OVER (ORDER BY month) AS prev_billed,
        ROUND((total_billed - LAG(total_billed) OVER (ORDER BY month))
            / NULLIF(LAG(total_billed) OVER (ORDER BY month), 0) * 100, 1) AS mom_growth_pct,
        ROUND(total_collected / NULLIF(total_billed, 0) * 100, 1) AS collection_rate_pct
    FROM monthly_summary
)
SELECT * FROM with_mom ORDER BY month;

What This Returns

The core monthly scorecard. mom_growth_pct shows month-over-month revenue growth — positive means the business is growing. collection_rate_pct shows how efficiently billed revenue converts to cash. A business billing more each month but with a declining collection rate may be growing into trouble.

The LAG(total_billed) OVER (ORDER BY month) pattern is the standard SQL technique for period-over-period comparison — it reaches back one row (one month earlier) to get the prior value.