Free SQL template

Monthly sales report

Revenue, orders and average order value per month, with the change on the month before.

Open in the SQL LabSave and export

It opens with this template loaded and runs in your browser. You don't need an account to use it; a free account saves your work and exports it.

The job it does

Every month-end: the numbers finance and the store managers ask for, as one query you can re-run. It comes with its own checks, so after you change it you can see at a glance that it still does the job.

How to use it with your own data

Import your own orders as a CSV (Database panel → Import a CSV) with an order date and an amount, then point the FROM at that table.

What's inside

This is the template exactly as the SQL Lab opens it, read-only. Open it to run it, change it and export it.

  • Runs on the retail sample database (Mangrove Market: stores, customers, orders, payments)
  • 2 tabs
  • PostgreSQL in your browser
The brief: Monthly sales report

Every month-end, finance and the store managers ask for the same numbers: orders, revenue and average order value per month, and how each month compares with the one before.

The report counts delivered orders only. Change the period in the WHERE clause; the second tab breaks one month down by store.

Your own data: import your orders as a CSV (Database → Import a CSV) with an order date and an amount, then change the FROM and the column names in the first CTE.

Done when
  • Has the columns month, orders, revenue, avg_order_value, revenue_change_pct
  • Returns between 1 and 120 rows
  • Does not change any data
monthly report
-- Monthly sales report: delivered orders per month, revenue, average order value,
-- and the change on the month before. Change the period in the WHERE clause.
WITH monthly AS (
  SELECT date_trunc('month', o.ordered_at)::date AS month,
         count(DISTINCT o.id) AS orders,
         sum(i.qty * i.unit_price_cents) / 100.0 AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
  WHERE o.status = 'delivered'
    AND o.ordered_at >= '2025-01-01' AND o.ordered_at < '2026-01-01'
  GROUP BY 1
)
SELECT month,
       orders,
       round(revenue, 2) AS revenue,
       round(revenue / orders, 2) AS avg_order_value,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month)) / lag(revenue) OVER (ORDER BY month), 1) AS revenue_change_pct
FROM monthly
ORDER BY month;
by store
-- The same month by store: which stores drive the change?
SELECT date_trunc('month', o.ordered_at)::date AS month,
       s.name AS store,
       count(DISTINCT o.id) AS orders,
       round(sum(i.qty * i.unit_price_cents) / 100.0, 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN stores s ON s.id = o.store_id
WHERE o.status = 'delivered'
  AND o.ordered_at >= '2025-12-01' AND o.ordered_at < '2026-01-01'
GROUP BY 1, 2
ORDER BY revenue DESC;
Open in the SQL Lab

The lesson behind it

Learn why it works, with a podcast and a quiz for each lesson.

  • Cohort retention

    Customers grouped by the month of their first order: how many come back in each later month.

  • Data-quality checks

    Duplicates, missing values, orphans and impossible values, each as one row with a count.

  • Schema design for a new feature

    Tables, keys and constraints for a new feature, sample rows, and the queries the feature needs.

All SQL templates · Every template