Clip wants a monthly view of its highest-revenue products by category. Write a PostgreSQL query that aggregates completed sales and uses a window function to rank products within each category and month.
sales to products and calculate revenue as quantity * unit_price.status = 'completed', then aggregate revenue by product, category, and calendar month.ROW_NUMBER() or RANK() partitioned by category and month to identify the top three products. Break revenue ties with the product ID.| Column | Type | Description |
|---|---|---|
| product_idPK | INTEGER | Unique product identifier |
| product_name | VARCHAR(100) | Clip product name |
| category | VARCHAR(50) | Product category |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INTEGER | Unique sale identifier |
| product_id | INTEGER | Product sold |
| sold_at | DATE | Date of sale |
| quantity | INTEGER | Units sold |
| unit_price | NUMERIC(10,2) | Price per unit |
| status | VARCHAR(20) | Sale processing status |