Home›Learn›Analytics

How to build a monthly revenue trend report

Month-over-month and year-over-year comparison using SQL window functions

Definition

What it is

A monthly revenue trend report aggregates revenue by calendar month and computes period-over-period comparisons — typically month-over-month (MoM) growth and year-over-year (YoY) growth. It is one of the most universally useful SQL reports for any business with transaction data: it converts a raw orders or transactions table into a meaningful time-series view that surfaces growth trends, seasonality, and anomalies.

Formula

MoM and YoY Growth Formula

Formula

MoM Growth (%) = ((Current Month Revenue − Prior Month Revenue) ÷ Prior Month Revenue) × 100 YoY Growth (%) = ((Current Month Revenue − Same Month Prior Year Revenue) ÷ Same Month Prior Year Revenue) × 100

In SQL, use the LAG() window function to access the previous period value: LAG(revenue, 1) OVER (ORDER BY month) gives prior month revenue. LAG(revenue, 12) gives same month prior year. This eliminates the need for a self-join and keeps the query clean.

Business impact

Why it matters

Raw revenue numbers without context are hard to interpret. A monthly revenue trend with period comparisons answers the questions that matter: Is the business growing? Is growth accelerating or decelerating? Is a slow month genuinely bad or just seasonal? The YoY comparison is particularly valuable for businesses with strong seasonality — it separates true performance from calendar effects.

The challenge

How most teams track this today

Most BI tools and dashboards show revenue trend charts, but the underlying data is typically pre-aggregated. Building the calculation yourself from raw transaction data gives you control over the definition of revenue (gross vs net, which order statuses to include) and the ability to filter by any dimension — product, channel, region.

Run it on your data

Calculate this automatically with Taptic Data

Connect your PostgreSQL account and Taptic generates this calculation from plain English against your actual data — no Excel exports, no manual joins. The SQL runs against your real schema, your real tables, your real numbers.

SQL query for monthly revenue trend with MoM and YoY comparison →Connect PostgreSQL →Try Free — $29.99/mo

FAQ

Common questions

What is the LAG() function in SQL and why use it for trend reports?

LAG() is a window function that returns the value from a previous row in the result set. For trend reports, LAG(revenue, 1) OVER (ORDER BY month) retrieves the prior month's revenue in the same row as the current month — allowing you to compute growth rate in a single SELECT without a self-join. It is cleaner, faster, and more readable than alternative approaches.

How do I handle months with zero revenue in a trend report?

If some months have no transactions, they will not appear in a GROUP BY query and the LAG() function will skip them, producing incorrect comparisons. The fix is to generate a complete date spine (a series of all months in your range) and LEFT JOIN your revenue data to it, filling in zeros for empty months.

Can this query work on Amazon and Shopify data in Taptic?

Yes. The monthly revenue trend query in Taptic works against any orders table — Amazon, Shopify, or a custom database table. The AI reads your actual schema to identify the date field and revenue field, so the generated SQL uses your real column names rather than generic placeholders.

What is the difference between MoM and WoW (week-over-week) trends?

MoM (month-over-month) smooths out daily and weekly noise and is better for strategic reporting. WoW (week-over-week) surfaces shorter-term patterns — useful for operational monitoring where you want to catch a sudden drop within days. The same LAG() approach works for both; just change the date truncation from month to week.

Analytics hub

All Analytics queries, use cases, and SQL examples in one place.

SQL Query Examples hub — queries for databases, warehouses, and eCommerce data →

Stop calculating this in spreadsheets

Connect PostgreSQL to Taptic Data and run this calculation automatically from plain English — against your real data, on a schedule, delivered to your team.

Start Free — $29.99/mo Try the Live Demo