SQL Aggregations SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Monthly Order Value Trend

Return one order-volume and value row per calendar year-month.

  • Aggregation
  • Date analysis
  • Type conversion
  • Sorting

Exercise brief

Understand the request

Revenue planning analyst The operating review needs monthly volume reconciled to populated order values.

Return one order-volume and value summary row per observed calendar month.

Return

  • Return order_year, order_month, total_orders, valued_orders, total_order_value in this exact left-to-right order.

Constraints

  • Return numeric year and month using the selected engine's date functions.
  • Count all orders separately from non-NULL amounts; preserve zero.
  • Sort by order_year, then order_month ascending.

Data you will use

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

orders

  • ordered_atDATETIME
  • order_amountDECIMAL(12,2)

Hints, when you need them

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

Hint 1

Derive both calendar components first; together they define monthly grain across year boundaries.

Hint 2

SQLite uses strftime, PostgreSQL uses EXTRACT, and MySQL or SQL Server use YEAR and MONTH.

Hint 3

SELECT /* numeric year expression */ AS order_year, /* numeric month expression */ AS order_month, COUNT(*) AS total_orders, COUNT(/* nullable measure */) AS valued_orders, SUM(/* measure */) AS total_order_value FROM orders GROUP BY /* same year and month expressions */ ORDER BY order_year, order_month;

Verified SQL answer

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

Reveal solution and explanation
SELECT CAST(strftime('%Y', ordered_at) AS INTEGER) AS order_year, CAST(strftime('%m', ordered_at) AS INTEGER) AS order_month, COUNT(*) AS total_orders, COUNT(order_amount) AS valued_orders, SUM(order_amount) AS total_order_value FROM orders GROUP BY CAST(strftime('%Y', ordered_at) AS INTEGER), CAST(strftime('%m', ordered_at) AS INTEGER) ORDER BY order_year, order_month;

Why this works

Correctness: year and month together define calendar-month grain, while row count and valued-order count expose NULL amounts without discarding their orders. Edge case: grouping only by month can merge the same month across years, and grouping by the full timestamp is too fine. Portability: date-part syntax differs by engine, so each solution preserves the same numeric year-month contract with its native functions.

Success check

Every observed month appears once in chronological order with correct metrics.

Expected result

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

order_yearorder_monthtotal_ordersvalued_orderstotal_order_value
2025111190
2025243180
2025322180
2025422300
2025522210
2025611120

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.