Your question is Top Products by Revenue per Category. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |