Number of Orders by Each Customer per Month

SQL coding challenge · Difficulty: medium · +75 XP

Problem

Count how many orders each customer placed in each month. Group the month down to its first day (e.g. 2024-01-01).

Note: MySQL has no DATE_TRUNC — use DATE_FORMAT(order_date, '%Y-%m-01') to get the month.

Tables

Table: sales

| order_id | customer_id | product_id | amount | order_date |
| --- | --- | --- | --- | --- |
| 201 | 1 | 11 | 100 | 2024-01-05 |
| 202 | 1 | 12 | 200 | 2024-01-20 |
| 203 | 1 | 11 | 150 | 2024-02-10 |
| 204 | 2 | 13 | 300 | 2024-01-15 |
| 205 | 2 | 12 | 250 | 2024-02-05 |
| 206 | 2 | 11 | 400 | 2024-02-25 |

Expected Output

| customer_id | month | order_count |
| --- | --- | --- |
| 1 | 2024-01-01 | 2 |
| 1 | 2024-02-01 | 1 |
| 2 | 2024-01-01 | 1 |
| 2 | 2024-02-01 | 2 |

Return: customer_id, month, order_count (ordered by customer_id, month)
Approach: COUNT(*) with GROUP BY customer_id, DATE_FORMAT(order_date,'%Y-%m-01')