revenue_dashboard.sql en SQL

Mes contra mes, acumulado del año, y la parte de cada mes frente al mejor mes hasta ahí.

-- Tablero de ingresos: crecimiento, impulso y el ranking que hay detrás.

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;

Cómo funciona

Arma los totales mensuales una vez en una CTE; cada columna de abajo los reutiliza.
Una sola ventana con nombre sirve al total acumulado y al pico acumulado.
LAG da el mes pasado, y la columna de crecimiento es la diferencia sobre él.

Palabras clave y builtins usados aquí

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

El intento, en números

Líneas: 54
Caracteres a escribir: 1166
Tokens: 248
Ritmo de tres estrellas: 100 tpm

Al ritmo de tres estrellas de 100 tokens por minuto, este intento toma unos 149 segundos.

Escribe este fragmento

Paso 2 de 2 en Bis; paso 24 de 24 en Analítica y reportes.

← Anterior