revenue_dashboard.sql in SQL

Month over month, year to date, and each month's share of the best month so far.

-- Revenue dashboard: growth, momentum, and the ranking behind it.

WITH monthly AS (
    SELECT
        STRFTIME('%Y-%m', placed_at) AS month,
        COUNT(*) AS orders_count,
        COUNT(DISTINCT customer_id) AS customers,
        ROUND(SUM(total), 2) AS revenue,
        ROUND(AVG(total), 2) AS avg_order
    FROM orders
    WHERE status = 'paid'
    GROUP BY STRFTIME('%Y-%m', placed_at)
),
enriched AS (
    SELECT
        month,
        orders_count,
        customers,
        revenue,
        avg_order,
        LAG(revenue) OVER by_month AS previous_revenue,
        ROUND(SUM(revenue) OVER by_month, 2) AS revenue_to_date,
        ROUND(MAX(revenue) OVER by_month, 2) AS best_so_far,
        ROUND(AVG(revenue) OVER rolling_quarter, 2) AS rolling_quarter
    FROM monthly
    WINDOW
        by_month AS (
            ORDER BY month
        ),
        rolling_quarter AS (
            ORDER BY month
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        )
)
SELECT
    month,
    orders_count,
    customers,
    revenue,
    avg_order,
    ROUND(revenue - previous_revenue, 2) AS growth,
    ROUND(
        (revenue - previous_revenue) * 100.0
        / NULLIF(previous_revenue, 0),
        1
    ) AS growth_pct,
    revenue_to_date,
    rolling_quarter,
    ROUND(revenue * 100.0 / NULLIF(best_so_far, 0), 1) AS pct_of_best,
    RANK() OVER (
        ORDER BY revenue DESC
    ) AS revenue_rank
FROM enriched
ORDER BY month DESC;

How it works

Build the monthly totals once in a CTE; every column below reuses them.
One named window serves the running total and the running peak.
LAG gives last month, and the growth column is the difference over it.

Keywords and builtins used here

AND
AS
AVG
BETWEEN
BY
COUNT
CURRENT
DESC
DISTINCT
FROM
GROUP
MAX
NULLIF
ORDER
ROW
ROWS
SELECT
SUM
WHERE
WITH
month

The run, in numbers

Lines: 54
Characters to type: 1159
Tokens: 248
Three-star pace: 100 tpm

At the three-star pace of 100 tokens a minute, this run takes about 149 seconds.

Type this snippet

Step 2 of 2 in Encore, step 24 of 24 in Analytics & reporting.

← Previous