Can you write a complex SQL query involving window functions and multiple joins to identify top-performing product categories?
Use the provided order, product, category, line-item, and return data. Consider completed orders from the current and prior comparison periods, subtract returned quantities, and return the three highest-ranked categories, including ties.
category_name, sales_rank, current_net_sales, prior_net_sales, sales_growth_pct, latest_month, latest_month_sales, previous_observed_month_sales, and month_over_month_change| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Product category identifier |
| category_name | VARCHAR(100) | Product category name |
| department_name | VARCHAR(100) | Merchandising department |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Product identifier |
| product_name | VARCHAR(150) | Product name |
| category_id | INT | Product category identifier |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Order identifier |
| order_date | DATE | Order date |
| order_status | VARCHAR(30) | Order lifecycle status |
| sales_channel | VARCHAR(30) | Sales channel |
| Column | Type | Description |
|---|---|---|
| line_item_idPK | INT | Order line identifier |
| order_id | INT | Parent order identifier |
| product_id | INT | Purchased product identifier |
| quantity | INT | Units purchased |
| unit_price | NUMERIC(10,2) | Price per unit |
| Column | Type | Description |
|---|---|---|
| return_idPK | INT | Return identifier |
| line_item_id | INT | Returned order line identifier |
| return_quantity | INT | Units returned |
| return_reason | VARCHAR(100) | Reason for return |