How do you optimize a slow-performing SQL query that involves multiple joins on large consumer databases at Ipsos?
Write a PostgreSQL query that returns the requested result while demonstrating an efficient approach for filtering, joining, and aggregating large survey datasets. The query should preserve valid completed responses and avoid unnecessary row multiplication.
project_id, region_code, completed_response_count, unique_consumer_count, and average_metric_score.project_id ascending, then region_code ascending, with NULL regions last.| Column | Type | Description |
|---|---|---|
| consumer_idPK | INT | Unique consumer identifier |
| region_code | VARCHAR(20) | Consumer geographic region |
| age_band | VARCHAR(20) | Consumer age category |
| consent_status | VARCHAR(20) | Research participation consent state |
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Survey project identifier |
| project_name | VARCHAR(150) | Ipsos survey project name |
| field_start | DATE | Fieldwork start date |
| field_end | DATE | Fieldwork end date |
| status | VARCHAR(20) | Project lifecycle status |
| Column | Type | Description |
|---|---|---|
| response_idPK | INT | Survey response identifier |
| project_id | INT | Referenced survey project |
| consumer_id | INT | Referenced consumer |
| completed_at | TIMESTAMP | Response completion timestamp |
| response_status | VARCHAR(20) | Response completion state |
| Column | Type | Description |
|---|---|---|
| answer_idPK | INT | Answer row identifier |
| response_id | INT | Referenced survey response |
| question_id | INT | Referenced survey question |
| numeric_value | NUMERIC(8,2) | Numeric answer value |
| Column | Type | Description |
|---|---|---|
| question_idPK | INT | Question identifier |
| metric_name | VARCHAR(100) | Metric represented by the question |
| is_target_metric | BOOLEAN | Whether the question contributes to the requested metric |