sales_report.sql in SQL

A monthly sales report built from chained CTEs, one step per stage.

-- Monthly revenue with growth and top product per month.
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;

How it works

Each CTE narrows or reshapes the rows the next one reads.
The window function adds month-over-month growth.
The final SELECT is short because the work happened above it.

Keywords and builtins used here

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

The run, in numbers

Lines: 63
Characters to type: 1153
Tokens: 300
Three-star pace: 95 tpm

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

Type this snippet

Step 1 of 2 in Encore, step 21 of 22 in Joins & aggregation.

← Previous Next →