Error Codes in the API Gateway Logs — SQL Strings Problem
Every line the API gateway writes starts with a code word — 'ERR-504', 'WARN-429', 'INFO-200' — followed by a space and free text.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #327
Problem statement
Every line the API gateway writes starts with a code word — 'ERR-504', 'WARN-429', 'INFO-200' — followed by a space and free text. Only a line whose message starts with 'ERR-' is an error; a line such as 'INFO-200 retried after ERR-504' was served fine and is not one.
Count the errors per code. Return the columns error_code (the first word of the message, e.g. 'ERR-504') and occurrences, ordered by occurrences descending, then error_code ascending.
Tables
Table: GatewayLog
| Column | Type |
|---|---|
| log_id | int |
| logged_at | datetime |
| message | varchar |
Primary key: log_id.
One row per log line. Every message is an upper-case code word of the form LEVEL-NNN, one space, and text.
Examples
Example 1
GatewayLog
| log_id | logged_at | message |
|---|---|---|
| 1 | 2025-06-01 10:00:02 | ERR-504 upstream timed out after 30s |
| 2 | 2025-06-01 10:00:05 | INFO-200 request served |
| 3 | 2025-06-01 10:00:09 | ERR-502 bad gateway from payments-svc |
| 4 | 2025-06-01 10:01:11 | ERR-504 upstream timed out after 30s |
| 5 | 2025-06-01 10:01:30 | INFO-200 retried after ERR-504 |
| 6 | 2025-06-01 10:02:44 | WARN-429 client throttled |
| 7 | 2025-06-01 10:03:01 | ERR-500 unhandled exception in handler |
| 8 | 2025-06-01 10:03:15 | ERR-502 bad gateway from search-svc |
Output
| error_code | occurrences |
|---|---|
| ERR-502 | 2 |
| ERR-504 | 2 |
| ERR-500 | 1 |
How to solve Error Codes in the API Gateway Logs
Two string operations do the work. First the filter: message LIKE 'ERR-%' keeps the lines whose text begins with 'ERR-'. The pattern has no leading %, which is the point — '%ERR-%' would also catch 'INFO-200 retried after ERR-504', a successful request that only mentions an earlier error. (An anchored LIKE can also use an index on the column; a leading wildcard cannot.)
Then the key: the code is the first word, so SUBSTRING_INDEX(message, ' ', 1) — everything before the first space — gives 'ERR-504'. The same expression goes in GROUP BY, and COUNT(*) counts the lines per code. LEFT(message, LOCATE(' ', message) - 1) is the same cut written with a position search.
Codes can tie on their count, so the order adds error_code as a second key. The whole query is one scan of the log.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(message, ' ', 1) AS error_code, COUNT(*) AS occurrences
FROM GatewayLog
WHERE message LIKE 'ERR-%'
GROUP BY SUBSTRING_INDEX(message, ' ', 1)
ORDER BY occurrences DESC, error_codeAnother way
SELECT LEFT(message, LOCATE(' ', message) - 1) AS error_code, COUNT(*) AS occurrences
FROM GatewayLog
WHERE LEFT(message, 4) = 'ERR-'
GROUP BY LEFT(message, LOCATE(' ', message) - 1)
ORDER BY occurrences DESC, error_code← Deployment Outcomes per Service · Production Incidents Opened on a Weekend →