Your question is Optimize Dashboard Revenue Join Query. Start with the requirements and the five 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.
You are given a dashboard query that joins several large tables to show monthly revenue by account, product, and region. Your task is to write a PostgreSQL query that returns the final dashboard result efficiently, while preserving correct totals and ranking the top products within each month. Assume the source tables are large enough that repeated scans and unnecessary row expansion matter.
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Primary account identifier |
| account_name | VARCHAR(255) | Customer account name |
| region_id | INT | Region assigned to the account |
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Primary region identifier |
| region_name | VARCHAR(100) | Human-readable region name |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Primary product identifier |
| product_name | VARCHAR(255) | Product name shown in the dashboard |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Primary order identifier |
| account_id | INT | Account that placed the order |
| order_date | DATE | Order date |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Primary line-item identifier |
| order_id | INT | Related order identifier |
| product_id | INT | Related product identifier |
| quantity | INT | Units sold on the line item |
| unit_price | NUMERIC(10,2) | Price per unit at the time of sale |