How do you approach SQL: Average Order Value by Month (DATE_TRUNC)?

Group by the truncated month - Postgres DATE_TRUNC('month', order_date), MySQL DATE_FORMAT(order_date, '%Y-%m').

What are common mistakes on SQL: Average Order Value by Month (DATE_TRUNC)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Average Order Value by Month (DATE_TRUNC)

MediumDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution.

Given Orders(id, order_date, total) where order_date is an ISO date string, return the average order value per calendar month rounded to 2 decimals. Sort by month ascending.

For the auto-grader: return [{ month, avg_total }] where month is "YYYY-MM".

Example 1

Input: orders = [{id:1,order_date:"2026-01-15",total:100},{id:2,order_date:"2026-01-20",total:200},{id:3,order_date:"2026-02-01",total:50}]

Output: [{month:"2026-01",avg_total:150},{month:"2026-02",avg_total:50}]

Constraints

- 0 <= orders.length <= 10^4 - order_date is "YYYY-MM-DD" - avg_total is rounded to 2 decimals with halves rounded UP (0.125 -> 0.13): JavaScript `Math.round(x * 100) / 100`, Python `math.floor(x * 100 + 0.5) / 100`. Python's built-in `round()` rounds halves to even (0.125 -> 0.12), so it does not match.

SQL: Average Order Value by Month (DATE_TRUNC): approach, complexity and pitfalls

A medium sql problem, graded against 6 test cases (4 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

Group by the truncated month - Postgres `DATE_TRUNC('month', order_date)`, MySQL `DATE_FORMAT(order_date, '%Y-%m')`.
AVG over the group, then format the bucket label.

The optimal approach

Show the approach: Canonical SQL (Postgres):

```sql

SELECT TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-MM') AS month,

ROUND(AVG(total)::numeric, 2) AS avg_total

FROM Orders

GROUP BY DATE_TRUNC('month', order_date)

ORDER BY month;

```

MySQL equivalent: `DATE_FORMAT(order_date, '%Y-%m')`. SQL Server: `FORMAT(order_date, 'yyyy-MM')` or `DATEPART(year/month, ...)`.

Performance trap: functions on the grouping column block index usage if you GROUP BY `DATE_TRUNC` directly. For very large tables, store a generated `order_month` column with an index, or use range filters per month and UNION ALL.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.