sales_report.sql en SQL

Un reporte mensual de ventas armado con CTE encadenadas, un paso por etapa.

-- Ingresos mensuales con crecimiento y el producto top de cada mes.
WITH paid AS (
    SELECT
        o.id,
        o.total,
        STRFTIME('%Y-%m', o.placed_at) AS month
    FROM orders AS o
    WHERE o.status = 'paid'
      AND o.placed_at >= DATE('now', '-12 months')
),
monthly AS (
    SELECT
        month,
        COUNT(*) AS orders,
        ROUND(SUM(total), 2) AS revenue,
        ROUND(AVG(total), 2) AS average_order
    FROM paid
    GROUP BY month
),
with_growth AS (
    SELECT
        month,
        orders,
        revenue,
        average_order,
        LAG(revenue) OVER (
            ORDER BY month
        ) AS previous_revenue
    FROM monthly
),
top_product AS (
    SELECT
        STRFTIME('%Y-%m', o.placed_at) AS month,
        p.name AS product,
        SUM(i.quantity) AS units,
        RANK() OVER (
            PARTITION BY STRFTIME('%Y-%m', o.placed_at)
            ORDER BY SUM(i.quantity) DESC
        ) AS position
    FROM orders AS o
    JOIN order_items AS i
        ON i.order_id = o.id
    JOIN products AS p
        ON p.sku = i.sku
    WHERE o.status = 'paid'
    GROUP BY month, p.name
)
SELECT
    g.month,
    g.orders,
    g.revenue,
    g.average_order,
    ROUND(
        (g.revenue - g.previous_revenue) * 100.0
        / NULLIF(g.previous_revenue, 0),
        1
    ) AS growth_pct,
    t.product AS best_seller
FROM with_growth AS g
LEFT JOIN top_product AS t
    ON t.month = g.month
   AND t.position = 1
ORDER BY g.month DESC;

Cómo funciona

Cada CTE acota o remodela las filas que lee la siguiente.
La función de ventana agrega el crecimiento mes a mes.
El SELECT final es corto porque el trabajo ocurrió arriba.

Palabras clave y builtins usados aquí

AND
AS
AVG
BY
COUNT
DATE
DESC
FROM
GROUP
JOIN
LEFT
NULLIF
ON
ORDER
SELECT
SUM
WHERE
WITH
g
month
position

El intento, en números

Líneas: 63
Caracteres a escribir: 1164
Tokens: 300
Ritmo de tres estrellas: 95 tpm

Al ritmo de tres estrellas de 95 tokens por minuto, este intento toma unos 189 segundos.

Escribe este fragmento

Paso 1 de 2 en Bis; paso 21 de 22 en Joins y agregación.

← Anterior Siguiente →