MediumBusiness Analytics High Frequency

Month-Over-Month (MoM) Revenue Growth Rate

Frequently Asked By:StripeUberAmazon

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Write a SQL query to calculate the Month-Over-Month (MoM) revenue growth rate for each calendar month. Return the calendar month formatted as YYYY-MM, current month revenue, previous month revenue, and the percentage growth rate rounded to 2 decimal places.

Why Technical Interviewers Ask This

Growth metrics form the bedrock of business intelligence engineering. Stripe and fintech companies test whether you can aggregate transaction logs chronologically and handle financial edge cases like the first month (which has no prior baseline) and zero revenue periods.

Common Pitfalls & Interview Traps

Division by zero when previous month revenue is $0 or NULL.

Performing string concatenation for dates instead of using DATE_TRUNC() with proper timezone handling.

Forgetting that integer division in some SQL dialects (like PostgreSQL) truncates decimals if not cast to NUMERIC or FLOAT.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH MonthlyAggregates AS (
  SELECT
    DATE_TRUNC('month', transaction_date)::date AS revenue_month,
    SUM(amount) AS current_month_revenue
  FROM transactions
  WHERE status = 'settled'
  GROUP BY 1
),
WithPriorMonth AS (
  SELECT
    revenue_month,
    current_month_revenue,
    LAG(current_month_revenue, 1) OVER (ORDER BY revenue_month ASC) AS prev_month_revenue
  FROM MonthlyAggregates
)
SELECT
  TO_CHAR(revenue_month, 'YYYY-MM') AS month_label,
  current_month_revenue,
  prev_month_revenue,
  ROUND(
    ((current_month_revenue - prev_month_revenue) / NULLIF(prev_month_revenue, 0)) * 100.0,
    2
  ) AS mom_growth_percentage
FROM WithPriorMonth
ORDER BY revenue_month ASC;

Approach Breakdown

We aggregate total settled revenue per month in a first CTE. Next, we use LAG(current_month_revenue, 1) to inspect the preceding month. Finally, we compute percentage delta using NULLIF(prev_month_revenue, 0) to guard against fatal division-by-zero errors in SQL.

Edge Cases to Verbally Clarify with Your Interviewer

The initial baseline month (prev_month_revenue is NULL, output growth percentage is NULL).

Months with zero sales (guarded with NULLIF).

Missing calendar months with no transactions (can be filled using generate_series if full calendar continuity is required).

Frequently Asked Follow-Up Questions

What is the purpose of NULLIF(prev_month_revenue, 0)?

If the previous month had 0 revenue, dividing by it causes a runtime exception in SQL ("division by zero"). NULLIF returns NULL whenever the argument equals 0, causing the division result to gracefully evaluate to NULL.