Task
NovaCare wants a quick view of which application errors are happening most often in recent customer logs. Write a SQL query to find the most common error_code values from the last 7 days.
Requirements
- Use only rows from the last 7 days based on
log_time.
- Exclude rows where
error_code is NULL.
- Return each
error_code and its occurrence count as error_count.
- Sort by
error_count descending, then error_code ascending.
Table Definition
customer_logs
| Column | Type | Description |
|---|
| log_id | INT | Primary key for each log row |
| customer_id | INT | Customer who generated the log |
| log_time | TIMESTAMP | Time the log was recorded |
| log_level | VARCHAR(20) | Severity level such as INFO or ERROR |
| error_code | VARCHAR(20) | Error code associated with the log |
| message | TEXT | Log message text |
Sample Data
| log_id | customer_id | log_time | log_level | error_code | message |
|---|
| 1 | 101 | 2024-06-14 09:12:00 | ERROR | E401 | Unauthorized token |
| 2 | 102 | 2024-06-13 11:45:00 | ERROR | E500 | Internal server error |
| 3 | 103 | 2024-06-12 08:30:00 | WARN | NULL | Slow response |
| 4 | 104 | 2024-06-10 14:05:00 | ERROR | E401 | Unauthorized token |
| 5 | 105 | 2024-06-09 16:20:00 | ERROR | E404 | Resource not found |
| 6 | 106 | 2024-06-07 10:00:00 | ERROR | E500 | Internal server error |
Expected Output
| error_code | error_count |
|---|
| E401 | 3 |
| E500 | 2 |
| E404 | 1 |
