Monthly Revenue Summary (Portable Month Bucketing)
Return order count and revenue by year-month across the full dataset.
- Aggregation
- Date analysis
- Numeric functions
- Sorting
Exercise brief
Understand the request
Business intelligence developer A portable monthly trend feed needs a stable sortable period key.
A portable monthly trend feed needs a stable sortable period key. Return order count and revenue by year-month across the full dataset.
Return
- Return order_month, order_count, monthly_revenue in this exact left-to-right order.
Constraints
- Bucket dates at calendar-month grain.
- Keep a chronologically sortable period value.
- Order by order_month.
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
Bucketing by month means deriving a 'YYYY-MM' label (or truncating to the 1st of the month).
Hint 2
GROUP BY the same month expression you SELECT.
Hint 3
SQLite: strftime('%Y-%m', d). Postgres: DATE_TRUNC('month', d). MySQL: DATE_FORMAT. SQL Server: FORMAT.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT strftime('%Y-%m', order_date) AS order_month, COUNT(*) AS order_count, ROUND(SUM(order_total), 2) AS monthly_revenue FROM orders GROUP BY strftime('%Y-%m', order_date) ORDER BY order_month;Why this works
Grouping by month is the single most common date task in analytics, and the canonical place engines diverge. Memorize the four idioms: SQLite strftime('%Y-%m'), Postgres DATE_TRUNC('month', d) (keeps a real date), MySQL DATE_FORMAT(d,'%Y-%m'), SQL Server FORMAT(d,'yyyy-MM'). DATE_TRUNC is preferred when you need a real date to sort or join on.
Success check
Every populated month appears once with correct order count and revenue.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_month | order_count | monthly_revenue |
|---|---|---|
| 2023-01 | 1 | 200 |
| 2023-02 | 1 | 340 |
| 2024-01 | 2 | 430 |
| 2024-02 | 2 | 470 |
| 2024-03 | 2 | 700 |
| 2024-04 | 2 | 540 |
| 2024-05 | 2 | 600 |
| 2024-06 | 2 | 700 |
| 2024-07 | 1 | 330 |
| 2024-08 | 1 | 270 |
Previewing 10 of 14 expected rows. Run the query in the editor to inspect the full result.
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.