NovaCart wants to analyze monthly sales trends across product categories. Write a PostgreSQL query to summarize completed order revenue over time.
Requirements
- Return one row per month and product category for completed orders only.
- Show the month as
YYYY-MM using PostgreSQL date formatting.
- Calculate:
- total revenue
- total quantity sold
- number of distinct completed orders
- a
revenue_trend flag that is 'up' when monthly revenue is at least 20% higher than 1000, otherwise 'stable'.
- Exclude rows where the product category is
NULL.
- Order the final output by month ascending, then category ascending.
Table Definitions
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Date the order was placed |
| status | VARCHAR(20) | Order status such as completed, cancelled, or pending |
order_items
| column | type | description |
|---|
| order_item_id | INT | Unique line item identifier |
| order_id | INT | Associated order |
| product_id | INT | Purchased product |
| quantity | INT | Units purchased |
| unit_price | DECIMAL(10,2) | Price per unit at purchase time |
products
| column | type | description |
|---|
| product_id | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
