94 SQL Challenge: Can You Extract the Year and Month From a Date?
Imagine your manager asks you to prepare a monthly sales report.
The report should show how much revenue the business generated in each month.
Your "orders" table contains complete dates, such as "2026-01-15".
How would you group these orders month by month?
Let's look at the data.
orders
| order_id | order_date | amount |
|---|---|---|
| 101 | 2026-01-05 | 5000 |
| 102 | 2026-01-15 | 3000 |
| 103 | 2026-02-10 | 7000 |
| 104 | 2026-02-20 | 4000 |
| 105 | 2026-03-12 | 6000 |
Your Challenge
Write an SQL query to:
- Extract the year from "order_date".
- Extract the month from "order_date".
- Calculate total sales for each month.
- Group the results by year and month.
- Sort the results by year and month.
Think before you scroll!
The SQL Solution
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
SUM(amount) AS total_sales
FROM orders
GROUP BY
EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY year, month;
Expected Output
| year | month | total_sales |
|---|---|---|
| 2026 | 1 | 8000 |
| 2026 | 2 | 11000 |
| 2026 | 3 | 6000 |
Why is this useful?
Monthly sales reports are common in real projects.
You can use the same approach to find:
- Monthly revenue
- Customer registrations
- Employee joining trends
- Monthly order volume
- Business growth over time
One Important Thing to Remember
Don't group only by month if your table contains data from multiple years.
For example, January 2025 and January 2026 are different reporting periods.
Always include the year along with the month.
Your Turn: Can you write the same query using "YEAR()" and "MONTH()"?
Share your SQL query in the comments. Let's learn together!
Top comments (0)