Task
You are given customer account data and AVEVA system event logs. Write a PostgreSQL query to identify customers who experienced more than one system_error event on the same calendar day. Return the customer ID, customer name, the error date, and the number of error events for that day. Only include active customers, and sort the results by error count descending, then by error date ascending, then by customer ID.
Schema
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer account name |
| status | VARCHAR(20) | Customer status such as active or inactive |
| | |
aveva_sites
| column | type | description |
|---|
| site_id | INT | Unique AVEVA site identifier |
| customer_id | INT | Customer that owns the site |
| site_name | VARCHAR(100) | Site name |
| | |
system_events
| column | type | description |
|---|
| event_id | INT | Unique event identifier |
| site_id | INT | Site where the event occurred |
| event_type | VARCHAR(50) | Event category |
| event_time | TIMESTAMP | Time of the event |
| severity | VARCHAR(20) | Event severity |
Sample data
Representative rows include active and inactive customers, non-error events, NULL severities, and sites without matching customers.
Expected output
| customer_id | customer_name | error_date | error_count |
|---|
| 1 | North Ridge Energy | 2024-04-10 | 3 |
| 3 | Delta Process Group | 2024-04-11 | 2 |
| 1 | North Ridge Energy | 2024-04-11 | 2 |
