You are given two Automation Anywhere tables: one lists customers who should have an active bot assignment, and the other lists the assignments that actually exist. Write a PostgreSQL query to return the customers that are missing a matching active assignment. Treat a customer as missing when they are marked as requiring an assignment but there is no row in the assignment table with the same customer_id, bot_name, and is_active = true.
Use the result to show the customer, expected bot, customer tier, and a status label of Missing Assignment. Return only missing records, ordered by customer_id and bot_name.
customer_bot_requirements| column | type | description |
|---|---|---|
| requirement_id | INT | Primary key for the requirement row |
| customer_id | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| customer_tier | VARCHAR(20) | Customer tier |
| bot_name | VARCHAR(100) | Bot that should be assigned |
| requires_assignment | BOOLEAN | Whether the bot is required |
bot_assignments| column | type | description |
|---|---|---|
| assignment_id | INT | Primary key for the assignment row |
| customer_id | INT | Customer identifier |
| bot_name | VARCHAR(100) | Assigned bot |
| assigned_on | DATE | Assignment date |
| is_active | BOOLEAN | Whether the assignment is currently active |
customer_bot_requirements: (2, 102, 'BluePeak Health', 'Enterprise', 'Document Automation', true)
customer_bot_requirements: (7, 107, 'Nova Retail', 'Growth', 'IQ Bot', true)
bot_assignments: (2, 102, 'Document Automation', '2024-02-11', false)
bot_assignments: (8, 110, 'Document Automation', '2024-02-07', true)
| customer_id | customer_name | customer_tier | bot_name | assignment_status |
|---|---|---|---|---|
| 102 | BluePeak Health | Enterprise | Document Automation | Missing Assignment |
| 107 | Nova Retail | Growth | IQ Bot | Missing Assignment |
| 109 | Cedar Finance | Enterprise | Document Automation | Missing Assignment |