Monthly Orders and Revenue

The basic query

A date like '2024-03-15' is unique to one day, so grouping by the raw column gives one group per date. In the orders table that's 15 groups for 15 orders. You need to group by a piece of the date instead.

In SQLite, strftime('%Y-%m', ...) returns a 'YYYY-MM' label:

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

This returns 9 rows, from 2024-01 (1 order, 249.99) to 2024-11 (1 order, 1199.99). September is the busiest month by count with 4 orders, but March brings in the most money: 2 orders for 3199.98.

The 'YYYY-MM' format also sorts correctly as text, which is why it's a better label than a month name like 'Mar'.

Why grouping by the month number alone fails

A common first attempt is strftime('%m', order_date) or MONTH(order_date). With this data it happens to work, because every order is from 2024. With more than one year it quietly merges months:

WITH t(order_date, amount) AS (
  VALUES ('2023-03-05', 100), ('2024-03-20', 50)
)
SELECT strftime('%m', order_date) AS month,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM t
GROUP BY month;

That returns a single row, '03' with 2 orders and 150, even though the orders are a year apart. Switch to '%Y-%m' and you get 2023-03 with 100 and 2024-03 with 50. If you want separate columns, GROUP BY yr, mo with strftime('%Y', ...) and strftime('%m', ...) works too.

The missing month problem

GROUP BY only creates groups for values that exist. The query above jumps from 2024-01 to 2024-03 and from 2024-04 to 2024-06, because nobody ordered in February or May. A chart built from it would draw a straight line over those months.

To show every month, build the list of months first and LEFT JOIN the orders onto it:

WITH RECURSIVE months(month) AS (
  SELECT '2024-01'
  UNION ALL
  SELECT strftime('%Y-%m', date(month || '-01', '+1 month'))
  FROM months
  WHERE month < '2024-11'
)
SELECT m.month,
       COUNT(o.order_id) AS order_count,
       ROUND(COALESCE(SUM(o.total_amount), 0), 2) AS revenue
FROM months m
LEFT JOIN orders o ON strftime('%Y-%m', o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

Now there are 11 rows, with 0 orders and 0 revenue for 2024-02 and 2024-05. Two details matter. COUNT(o.order_id) skips NULLs, so empty months count as 0, while COUNT(*) would show 1. And SUM over no rows is NULL, so it needs COALESCE. If recursive CTEs are new to you, Org Chart Hierarchy With a Recursive CTE walks through how the recursion runs.

Filters go before the grouping

A WHERE clause runs before GROUP BY, so it changes what each month contains. Add WHERE status = 'delivered' to the basic query and you get 7 months instead of 9: September drops from 4 orders to 3, and October and November disappear because their orders are still shipped or pending. To filter on the monthly totals instead, use HAVING.

Other databases

The idea is the same everywhere, only the date function changes. PostgreSQL and Snowflake have DATE_TRUNC('month', order_date), which returns the first day of the month as a date. That keeps the column a real date, so it sorts and joins cleanly. SQL Server uses YEAR() and MONTH() or DATEFROMPARTS. MySQL has DATE_FORMAT(order_date, '%Y-%m'). In SQLite, date(order_date, 'start of month') gives the same first-of-month date as DATE_TRUNC.

Once you have one row per month, turning months into columns is a separate step, covered in Pivot Rows Into Columns.

What to say in the interview

Say that you group by year and month together, and name the function for the database they use. Then ask whether months with no orders should appear. It's a natural follow-up, and raising it yourself shows you've thought about how the report will be read. More aggregation practice is in the GROUP BY interview questions.