Your question is Optimize Slow Multi-Join Query. Start with the requirements and the four 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.
How would you optimize a slow-running SQL query that involves multiple joins and aggregations on tables containing millions of rows?
Using the supplied schema, write the query that returns filtered completed-order totals by region, month, and product category. Explain the performance decisions and how you would validate them with PostgreSQL execution plans.
region, order_month, category, order_count, units_sold, and gross_revenue.order_month, region, and category.| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Account identifier |
| region | VARCHAR(40) | Account region |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Order identifier |
| account_id | INT | Account that placed the order |
| order_date | DATE | Order date |
| status | VARCHAR(20) | Order processing status |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Order line identifier |
| order_id | INT | Parent order identifier |
| product_id | INT | Referenced product identifier |
| quantity | INT | Units on the order line |
| unit_price | DECIMAL(12,2) | Price per unit |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Product identifier |
| category | VARCHAR(40) | Product category |