Error Codes in the API Gateway Logs

Easy
327.

Error Codes in the API Gateway Logs

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

ColumnType
log_idint
logged_atdatetime
messagevarchar

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.

Example

The rows must come back in the order shown.

GatewayLog

log_idlogged_atmessage
12025-06-01 10:00:02ERR-504 upstream timed out after 30s
22025-06-01 10:00:05INFO-200 request served
32025-06-01 10:00:09ERR-502 bad gateway from payments-svc
42025-06-01 10:01:11ERR-504 upstream timed out after 30s
52025-06-01 10:01:30INFO-200 retried after ERR-504
62025-06-01 10:02:44WARN-429 client throttled
72025-06-01 10:03:01ERR-500 unhandled exception in handler
82025-06-01 10:03:15ERR-502 bad gateway from search-svc

Output

error_codeoccurrences
ERR-5022
ERR-5042
ERR-5001
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
log_idint
logged_atdatetime
messagevarchar

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_idlogged_atmessage
12025-06-01 10:00:02ERR-504 upstream timed out after 30s
22025-06-01 10:00:05INFO-200 request served
32025-06-01 10:00:09ERR-502 bad gateway from payments-svc
42025-06-01 10:01:11ERR-504 upstream timed out after 30s
52025-06-01 10:01:30INFO-200 retried after ERR-504
62025-06-01 10:02:44WARN-429 client throttled
72025-06-01 10:03:01ERR-500 unhandled exception in handler
82025-06-01 10:03:15ERR-502 bad gateway from search-svc

Output

error_codeoccurrences
ERR-5022
ERR-5042
ERR-5001

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_code

Another 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 →