Date Operations & Time-Based Analytics SQL Topic exerciseHardVerified answerSQLite + MySQL + SQL Server live · 2 guided

Compare Like-for-Like Year-over-Year Months

Return revenue and YoY growth for calendar months populated in both years.

  • Subqueries
  • Aggregation
  • CASE expressions
  • Date analysis
  • Numeric functions

Exercise brief

Understand the request

Finance strategy analyst A board metric must compare only months available in both 2023 and 2024.

A board metric must compare only months available in both 2023 and 2024. Return revenue and YoY growth for calendar months populated in both years.

Return

  • Return month_num, current_year_sales, previous_year_sales, yoy_growth_percentage in this exact left-to-right order.

Constraints

  • Aggregate by year and month before pivoting.
  • Exclude months missing either comparison year.
  • Guard the growth denominator and order by month number.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • order_dateDATE
  • order_totalDECIMAL

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Aggregate the two years at month grain in the first CTE.

Hint 2

Pivot current and previous revenue with conditional aggregates.

Hint 3

Filter to two-sided comparisons and divide by NULLIF(previous_year_sales, 0).

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH monthly_sales AS (SELECT CAST(strftime('%m', order_date) AS INTEGER) AS month_num, CAST(strftime('%Y', order_date) AS INTEGER) AS year, SUM(order_total) AS monthly_revenue FROM orders WHERE strftime('%Y', order_date) IN ('2023', '2024') GROUP BY strftime('%Y', order_date), strftime('%m', order_date)), compared AS (SELECT month_num, MAX(CASE WHEN year = 2024 THEN monthly_revenue END) AS current_year_sales, MAX(CASE WHEN year = 2023 THEN monthly_revenue END) AS previous_year_sales FROM monthly_sales GROUP BY month_num) SELECT month_num, current_year_sales, previous_year_sales, ROUND((current_year_sales - previous_year_sales) * 100.0 / NULLIF(previous_year_sales, 0), 2) AS yoy_growth_percentage FROM compared WHERE current_year_sales IS NOT NULL AND previous_year_sales IS NOT NULL ORDER BY month_num;

Why this works

A like-for-like comparison excludes structurally missing months. NULLIF also prevents an undefined percentage when prior-period revenue is zero.

Success check

Only January and February are returned, with no misleading NULL comparison rows.

Expected result

Use this output to verify values, aliases, ordering, and row count.

month_numcurrent_year_salesprevious_year_salesyoy_growth_percentage
1430200115
247034038.24

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.