Task
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 |
Sample Data
Representative data is provided below in structured form. It includes cancelled and pending orders, a product with NULL category, zero quantity, and an order item with no matching product.
Expected Output
| month | category | total_revenue | total_quantity | completed_orders | revenue_trend |
|---|
| 2024-01 | Books | 325.00 | 5 | 2 | stable |
| 2024-01 | Electronics | 1200.00 | 3 | 2 | stable |
| 2024-02 | Books | 150.00 | 3 | 1 | stable |
| 2024-02 | Clothing | 200.00 | 4 | 1 | stable |
| 2024-02 | Electronics | 2400.00 | 3 | 2 | up |
| 2024-03 | Books | 75.00 | 1 | 1 | stable |
| 2024-03 | Clothing | 0.00 | 0 | 1 | stable |
