#0084

Monthly Revenue With CTE

MediumSQL20 min15 XP

Problem

Use a CTE to aggregate total revenue by month from orders(order_date, amount). Return month and revenue sorted by month.

CTEs make multi-step SQL readable and are common in production analytics queries.

orders(order_id INTEGER, order_date TEXT, amount REAL)

Example 1

Inputtwo January orders and one February order

Outputone row per month

substr(order_date,1,7) extracts YYYY-MM.

CodeSQL

WITH monthly AS (
  -- write your code here
)
SELECT * FROM monthly;

WITH monthly AS (
  -- write your code here
)
SELECT * FROM monthly;

Visible browser tests run here when available.

Testcases1 visible / 3 hidden categories

Two months

Inputorders table

Expectedmonthly totals

January rows are summed together.

Hidden Test Categories

single monthdecimal revenueunordered input dates