Gateway Connection Timeouts

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

TripAdvisor

Problem

During an on-call investigation, find all error tracking entries where the service name contains 'gateway' and the message contains the phrase 'timed out'.

Timeouts at the gateway.

Tables

err_tracks

Columns: err_id INTEGER, err_type TEXT, message TEXT, svc_name TEXT, severity TEXT, count INTEGER, first_at TEXT

Expected output

Worked solution and explanation

What this is really asking

This is a containment search wearing an incident costume. The word 'contains' is the whole problem: 'timed out' never stands alone in message, it sits inside 'Connection timed out after 30s', so any predicate that compares the whole cell finds nothing. Anyone can write the WHERE; what separates candidates is reading 'contains' as a substring with wildcards on both sides. Write message = 'timed out' or an anchored LIKE 'timed out%' and the query returns zero rows in the middle of an incident, and the on-call concludes the gateway has no timeout problem.

Break down the requirements

Step 1: Match the service as a substring of svc_name

svc_name LIKE '%gateway%' reads the requirement literally: 'gateway' anywhere in the name. On err_tracks the service is stored as exactly 'gateway', so today the pattern and an equality agree. The wildcards are what the prompt asked for, and they keep the query right the day a 'gateway-eu' starts logging.

Step 2: Find the phrase inside message

The only timeout text in the table is 'Connection timed out after 30s'. The phrase starts after 'Connection ', so message = 'timed out' and LIKE 'timed out%' both miss it. Only LIKE '%timed out%' finds it wherever it sits.

Step 3: AND, not OR

Both conditions must hold on the same row. Timeouts also land on 'user-svc' and the other services, and 'gateway' logs plenty of non-timeout errors such as 'File /tmp/data.csv not found'. With OR you would return all of them.

The solution

SELECT
  err_id,
  err_type,
  message,
  svc_name,
  severity,
  count,
  first_at
FROM err_tracks
WHERE svc_name LIKE '%gateway%'
  AND message LIKE '%timed out%'

Common follow-up questions

Common follow-up questions

• What if service names started arriving as 'Gateway' or 'GATEWAY'? (Tests whether you normalize case (`ILIKE`, or lowercasing both sides) instead of trusting today's data.)
• Also match the single word 'timeout' (no space). (Pushes to a regular expression such as `REGEXP_LIKE(message, 'timed ?out')`, or two `LIKE` clauses joined with `OR` inside parentheses.)
• Make this query fast on 20M rows. (Opens trigram indexes, partition pruning on `first_at`, or moving log search to a search engine.)