MoM, YoY, WoW — The SQL Patterns

This lesson on MoM, YoY, WoW — The SQL Patterns is hands-on and example-driven. You will master calculating period-over-period metrics (MoM, YoY, WoW) in SQL using date extraction functions, window offset functions, and chained Common Table Expressions (CTEs). You will be able to structure multi-step aggregations to compute sales totals, retrieve baseline figures, and format percentage growth with decimal precision.

What You'll Be Able To Do

• Extract calendar month names and chronological month numbers using DATENAME and DATEPART.
• Aggregate sales transactions by month while retaining numeric order for deterministic windowing.
• Apply LAG with offset and default fallback arguments to fetch previous period metrics.
• Chain multiple CTEs to isolate aggregation, lag calculation, and final growth rate arithmetic.
• Calculate percentage growth and format the output using CAST AS DECIMAL(7, 2).

Detailed Concept Walkthrough

1. Date Partitioning and Base Aggregation

Before calculating period-over-period differences, raw transactional data must be aggregated to the target time grain (e.g., monthly) and paired with a chronological sorting key.

• Mechanism: Use DATENAME(month, SalesDate) to get the display name of the month and DATEPART(month, SalesDate) to generate an integer representation (1-12). Both expressions must be included in the GROUP BY clause to aggregate SUM(Sales) correctly.
• Under the Hood: Relational engines group rows sharing identical month names and numbers into single aggregated bucket rows before evaluating window operations in subsequent CTE layers.
• Best Practice: Always retain the numeric month_number alongside the text month_name because sorting alphabetically by month name yields incorrect chronological order (e.g., April before February).

SELECT 
  DATENAME(month, s.SalesDate) AS month_name,
  DATEPART(month, s.SalesDate) AS month_number,
  SUM(s.Sales) AS total_sale
FROM dbo.sales_data s
GROUP BY 
  DATENAME(month, s.SalesDate),
  DATEPART(month, s.SalesDate);

Key Takeaway: Always extract and group by numeric date parts alongside string date names to ensure chronological ordering downstream.

2. Previous Period Value Retrieval via LAG

The LAG window function accesses data from a previous row at a specified physical offset without needing a self-join.

• Mechanism: LAG accepts three parameters: the target expression (total_sale), the row offset (1), and an optional default fallback value if no prior row exists within the partition.
• Under the Hood: The OVER (ORDER BY month_number) clause establishes the window frame order, allowing the query engine to peek exactly one row backward based on month sequence.
• Best Practice: Passing the current row's value (total_sale) as the 3rd argument to LAG prevents NULL values on the first row (January), setting the baseline difference to 0.

SELECT 
  c.month_name,
  c.total_sale,
  LAG(c.total_sale, 1, c.total_sale) OVER (
    ORDER BY c.month_number
  ) AS previous_sales_amount
FROM c_month_sales c;

Key Takeaway: Use LAG with an explicit ORDER BY and fallback default to fetch prior period baselines cleanly without generating NULLs.

3. Chained CTEs and Growth Arithmetic

Chaining CTEs creates a clean, readable pipeline that isolates aggregation, offset fetching, and final mathematical formulas into modular steps.

• Mechanism: The first CTE aggregates base metrics, the second CTE extracts previous period amounts with LAG, and the outer query performs difference and percentage growth arithmetic.
• Execution Flow: The final query subtracts previous_sales_amount from total_sale, divides by previous_sales_amount, multiplies by 100, and casts the result.
• Syntax Rule: To avoid integer truncation and imprecise floating points, wrap the growth calculation in CAST(... AS DECIMAL(7, 2)) to ensure two decimal places of precision.

WITH c_month_sales AS (
  SELECT 
    DATENAME(month, s.SalesDate) AS month_name,
    DATEPART(month, s.SalesDate) AS month_number,
    SUM(s.Sales) AS total_sale
  FROM dbo.sales_data s
  GROUP BY DATENAME(month, s.SalesDate), DATEPART(month, s.SalesDate)
),
c_previous_month AS (
  SELECT 
    c.month_name,
    c.month_number,
    c.total_sale,
    LAG(c.total_sale, 1, c.total_sale) OVER (ORDER BY c.month_number) AS previous_sales_amount
  FROM c_month_sales c
)
SELECT 
  cp.month_name,
  cp.total_sale,
  cp.previous_sales_amount,
  CAST(
    ((cp.total_sale - cp.previous_sales_amount) / cp.previous_sales_amount) * 100 
    AS DECIMAL(7, 2)
  ) AS month_over_month_growth
FROM c_previous_month cp
ORDER BY cp.month_number;

Key Takeaway: Structure complex multi-step analytical metrics across chained CTEs to keep calculation logic isolated, maintainable, and readable.

Topics Covered in MoM, YoY, WoW — The SQL Patterns

1. Problem Overview (0:00 - 0:45) — Examines the sales dataset schema and defines the target MoM output format.
2. Date Extraction and Aggregation (0:45 - 1:35) — Extracts month name and month number to aggregate total sales by month.
3. First CTE & LAG Window Function (1:35 - 2:40) — Wraps base aggregation in a CTE and implements LAG to pull prior month sales.
4. Fallback Defaults in LAG (2:40 - 3:25) — Explains why January receives current sales as its baseline using the default argument.
5. Chained CTE & Growth Math (3:25 - 4:15) — Builds second CTE to compute difference and percentage growth between months.
6. Decimal Casting and Final Output (4:15 - 4:50) — Applies DECIMAL(7, 2) casting to format final MoM growth percentage.

SQL Cheat Sheet

• DATENAME(datepart, date) — Returns string name of specified date component

  SELECT DATENAME(month, SalesDate) AS month_name FROM dbo.sales_data;
  

• DATEPART(datepart, date) — Returns integer value of specified date component

  SELECT DATEPART(month, SalesDate) AS month_num FROM dbo.sales_data;
  

• LAG(col, offset, default) OVER (ORDER BY ...) — Fetches value from prior row with fallback

  LAG(total_sale, 1, total_sale) OVER (ORDER BY month_number)
  

• WITH cte1 AS (...), cte2 AS (...) SELECT ... — Chains multiple common table expressions sequentially

  WITH cte1 AS (SELECT 1 AS id), cte2 AS (SELECT id FROM cte1) SELECT * FROM cte2;
  

• CAST(expression AS DECIMAL(p, s)) — Converts numeric output to exact precision/scale

  CAST(growth_val AS DECIMAL(7, 2))
  

Comparison Table

Common Pitfalls

• Mistake: Ordering LAG by month name string. Avoid: Always sort window functions by numeric DATEPART values.
• Mistake: Omitting non-aggregated date parts from GROUP BY. Avoid: Include every non-aggregated SELECT expression in GROUP BY.
• Mistake: Dividing by previous sales without handling zero or nulls. Avoid: Provide a default in LAG or guard divisions.
• Mistake: Forgetting to cast percentage calculations. Avoid: Wrap final percentage expressions in CAST AS DECIMAL(7, 2).

FAQs

• Why use DATEPART when DATENAME already gets the month? DATENAME returns string names like 'April' and 'January', which sort alphabetically rather than chronologically. DATEPART returns numbers (1-12) to guarantee correct temporal window ordering.
• Why does January show identical total_sale and previous_sales_amount? Because the LAG function's 3rd parameter was supplied as c.total_sale, returning the current row's sales as a fallback when no prior row exists.
• Can WoW and YoY calculations use this identical pattern? Yes. Change the date grain to week or year in DATEPART/DATENAME, partition by year/brand if necessary, and lag by 1 offset.