Date Operations & Time-Based Analytics SQL Topic exerciseEasyVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Extract Month and Year from Order Date

Group orders by calendar year and month and return the order count for each populated month.

  • Aggregation
  • Date analysis
  • Type conversion
  • Sorting

Exercise brief

Understand the request

Finance reporting analyst A reporting feed needs numeric calendar parts before downstream period labels are applied.

A reporting feed needs numeric calendar parts before downstream period labels are applied. Group orders by calendar year and month and return the order count for each populated month.

Return

  • Return year, month, order_count in this exact left-to-right order.

Constraints

  • Return numeric year and month values.
  • Group by both extracted date parts.
  • Order chronologically by year and month.

Data you will use

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

orders

  • order_idINTEGER
  • order_dateDATE

Hints, when you need them

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

Hint 1

Pulling year/month out of a date is "date part extraction".

Hint 2

SQLite: strftime('%Y', d) and strftime('%m', d) — both return text, so CAST to INTEGER.

Hint 3

GROUP BY the same extraction expressions you SELECT.

Verified SQL answer

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

Reveal solution and explanation
SELECT CAST(strftime('%Y', order_date) AS INTEGER) AS year, CAST(strftime('%m', order_date) AS INTEGER) AS month, COUNT(*) AS order_count FROM orders GROUP BY strftime('%Y', order_date), strftime('%m', order_date) ORDER BY year, month;

Why this works

Extracting parts of a date powers every time-bucketed report. SQLite uses strftime with format codes; the SQL standard (Postgres) uses EXTRACT(YEAR FROM d); MySQL has YEAR()/MONTH(); SQL Server uses DATEPART or YEAR()/MONTH(). All return numbers except SQLite's strftime, which returns text.

Success check

Each populated calendar month appears once with the correct count and chronological ordering.

Expected result

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

yearmonthorder_count
202311
202321
202412
202422
202432
202442
202452
202462
202471
202481

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

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.