Your question is Join Customer Tickets and Error Logs. 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 customer, ticket, and error-log data for an Aveva support workflow. Write a PostgreSQL query that joins the three tables to help troubleshoot a recurring issue by returning each customer’s open or recently closed tickets with the matching error details, including a flag that marks whether the error appears more than once for that customer.
Use the data to surface only tickets tied to the recurring error pattern and order the result so the most recent ticket appears first.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Primary key for the customer |
| customer_name | VARCHAR(100) | Customer display name |
| account_tier | VARCHAR(50) | Support tier or contract level |
| Column | Type | Description |
|---|---|---|
| ticket_idPK | INT | Primary key for the ticket |
| customer_id | INT | References customers.customer_id |
| opened_at | TIMESTAMP | Timestamp when the ticket was opened |
| closed_at | TIMESTAMP | Timestamp when the ticket was closed |
| status | VARCHAR(30) | Ticket status |
| issue_code | VARCHAR(50) | Issue code reported on the ticket |
| Column | Type | Description |
|---|---|---|
| error_idPK | INT | Primary key for the error event |
| customer_id | INT | References customers.customer_id |
| occurred_at | TIMESTAMP | Timestamp when the error occurred |
| error_code | VARCHAR(50) | Machine-readable error code |
| error_message | TEXT | Human-readable error message |