Timeout Warning Logs

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Vesta Innovations

Problem

During an incident postmortem, the on-call engineer needs all available fields for warning-level log entries where the message mentions a timeout.

Timeout warnings. The postmortem trail.

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

Worked solution and explanation

What this really is

This is a two-predicate filter dressed up as an incident postmortem. Anyone gets the log_level = 'WARN' half right. What separates candidates is reading 'mentions a timeout' as a substring test, message LIKE '%timeout%', and keeping both halves joined by AND. Write message = 'timeout' and you get zero rows, because every message is a full sentence like 'Connection timeout after 30s'. Drop either half and you get one of two failures: every warning in the table, or timeout lines logged at INFO and ERROR buried among the real warnings. During a live incident, an empty result reads as 'no timeouts happened'.

Break down the requirements

Step 1: Confirm the literal

Ask whether message must contain the substring timeout or also variants like timed out. The brief says 'mentions a timeout', so a LIKE '%timeout%' substring match on message is the agreed interpretation.

Step 2: Pin the severity literal

Confirm that log_level is stored as 'WARN' (not 'WARNING' or 'warn'). String equality in WHERE is case sensitive in most engines, and one wrong literal returns zero rows after a 50M-row scan.

Step 3: Select all fields

The postmortem needs every column, so SELECT * is correct here. Do not project a subset: on-call wants response_time_ms, server_name and log_timestamp alongside message.

Step 4: Combine with AND

Join the two predicates on log_level and message with AND. You need no IN list or OR chain, because there is one severity and one substring.

The solution

SELECT *
FROM server_logs
WHERE log_level = 'WARN'
  AND message LIKE '%timeout%'

Common follow-up questions

Common follow-up questions

• How would you handle case-insensitive matching on `log_level` and `message` without killing performance? (Probes awareness that wrapping the column in `LOWER()` defeats indexes; the fix is a functional index or a stored normalized column.)
• If the postmortem covers a 6-hour window, how do you rewrite this to exploit the `log_timestamp` partition? (Tests partition pruning intuition: a `log_timestamp BETWEEN` predicate lets the scan touch one or two partitions instead of all 180.)
• What index would you add if this query runs hourly? (Tests whether you know a leading-wildcard `LIKE` cannot use a B-tree; the answer is a trigram index (Postgres `pg_trgm`) or a full-text index on `message`.)
• How would you extend this to match `timeout`, `timed out`, and `timing out` in one pass? (Probes regex versus OR-chain trade-offs: one `REGEXP_LIKE` on `tim(e|ing|ed) ?out` versus three OR clauses.)