Task
NovaCart wants a simple monthly sales report for completed orders. Write a SQL query to summarize revenue and order volume by month using only the orders table.
Requirements
- Return one row per month for completed orders only.
- Show the month as
YYYY-MM, the number of completed orders, and total revenue.
- Sort the result by month ascending.
Table Definition
orders
| column_name | type | description |
|---|
| order_id | INT | Unique order identifier |
| customer_name | VARCHAR(100) | Customer name |
| order_date | DATE | Date the order was placed |
| status | VARCHAR(20) | Order status such as completed, cancelled, or pending |
| amount | DECIMAL(10,2) | Order amount |
Sample Data
| order_id | customer_name | order_date | status | amount |
|---|
| 1 | Maya Chen | 2024-03-05 | completed | 120.50 |
| 2 | Liam Patel | 2024-01-15 | cancelled | 80.00 |
| 3 | Ava Johnson | 2024-02-10 | completed | 200.00 |
| 4 | Noah Kim | 2024-01-20 | completed | 150.00 |
| 5 | Emma Davis | 2024-03-01 | pending | 95.00 |
| 6 | Maya Chen | 2024-02-18 | completed | 50.00 |
| 7 | Olivia Brown | 2024-01-03 | completed | 0.00 |
| 8 | Ethan Wilson | 2024-03-22 | completed | 300.00 |
| 9 | Sophia Lee | 2024-02-25 | completed | NULL |
| 10 | Liam Patel | 2024-01-28 | completed | 75.25 |
Expected Output
| order_month | completed_orders | total_revenue |
|---|
| 2024-01 | 3 | 225.25 |
| 2024-02 | 3 | 250.00 |
| 2024-03 | 2 | 420.50 |
