How do you optimize query performance in a distributed data warehouse when dealing with massive join operations?
Use the supplied PostgreSQL tables to write a query that returns metrics for every active entity. Include only current aliases, events from 2025-01-01 onward, and confirmed matches with a score of at least 0.80 linked to a current alias. The result should avoid multiplying rows across independent one-to-many relationships.
entity_id, entity_name, current_alias_count, recent_event_count, confirmed_match_count, max_match_score.max_match_score descending with nulls last, then entity_id ascending.| Column | Type | Description |
|---|---|---|
| entity_idPK | INT | Unique entity identifier |
| entity_name | VARCHAR(100) | Entity display name |
| region | VARCHAR(50) | Entity operating region |
| is_active | BOOLEAN | Whether the entity is active |
| Column | Type | Description |
|---|---|---|
| alias_idPK | INT | Unique alias identifier |
| entity_id | INT | Referenced entity |
| alias_text | VARCHAR(150) | Known alternate entity name |
| alias_type | VARCHAR(40) | Alias classification |
| is_current | BOOLEAN | Whether the alias is currently valid |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| entity_id | INT | Referenced entity |
| event_date | DATE | Date of the relationship event |
| event_type | VARCHAR(40) | Event category |
| severity | INT | Optional event severity |
| Column | Type | Description |
|---|---|---|
| match_idPK | INT | Unique match identifier |
| entity_id | INT | Matched entity |
| alias_id | INT | Alias involved in the match |
| match_score | NUMERIC(4,3) | Match confidence score |
| status | VARCHAR(30) | Match review status |
| matched_on | DATE | Date the match was created |