Calculate May 2024 Month-to-Date Revenue
Calculate daily revenue and month-to-date revenue for May 2024.
- Window functions
- Aggregation
- Date analysis
- Filtering
- Sorting
Exercise brief
Understand the request
Revenue operations analyst A month-close review needs daily May revenue and the cumulative total through each observed day.
A month-close review needs daily May revenue and the cumulative total through each observed day. Calculate daily revenue and month-to-date revenue for May 2024.
Return
- Return order_date, daily_revenue, mtd_revenue in this exact left-to-right order.
Constraints
- Filter to May 2024 with a deterministic period.
- Aggregate to day grain before applying the running total.
- Use an explicit ROWS frame and order by order_date.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERorder_dateDATEorder_totalDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Filter the source rows to the requested year-month first.
Hint 2
SUM per order_date produces daily revenue.
Hint 3
Apply a windowed SUM to those daily totals with an explicit cumulative ROWS frame.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT order_date, SUM(order_total) AS daily_revenue, SUM(SUM(order_total)) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mtd_revenue FROM orders WHERE strftime('%Y-%m', order_date) = '2024-05' GROUP BY order_date ORDER BY order_date;Why this works
MTD is a running total within a fixed month. Aggregating first prevents multiple orders on one date from creating duplicate day rows.
Success check
Both observed May dates appear in order and the second MTD value equals total May revenue.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_date | daily_revenue | mtd_revenue |
|---|---|---|
| 2024-05-05 | 220 | 220 |
| 2024-05-20 | 380 | 600 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.