Your question is Flag Multi-Source Alert Relationships. Start with the requirements and the three 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 small schema that models entities, alerts, and analyst investigations in a Foundry-style operational dataset. Write a PostgreSQL query that returns each entity created in January 2024 that has alerts from at least two distinct sources and at least one open investigation. For each qualifying entity, return the entity name, the number of distinct alert sources, the total number of alerts in January 2024, and a priority label of high when the entity has 3 or more January alerts, otherwise medium.
Because you do not have a running database, you should reason from the schema and sample data and produce the exact result set shown below.
| Column | Type | Description |
|---|---|---|
| entity_idPK | INT | Primary key for the entity |
| entity_name | VARCHAR(100) | Display name for the entity |
| entity_type | VARCHAR(50) | Type of entity such as device, account, or person |
| created_at | DATE | Date the entity record was created |
| Column | Type | Description |
|---|---|---|
| alert_idPK | INT | Primary key for the alert |
| entity_id | INT | Entity associated with the alert |
| source_system | VARCHAR(50) | System that generated the alert |
| severity | VARCHAR(20) | Alert severity level |
| alert_date | DATE | Date the alert was generated |
| Column | Type | Description |
|---|---|---|
| investigation_idPK | INT | Primary key for the investigation |
| entity_id | INT | Entity under investigation |
| status | VARCHAR(20) | Current investigation status |
| opened_at | DATE | Date the investigation was opened |