First and Last Timeout Per Service

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

Domain
SQL
Difficulty
medium
Seniority
L3
Asked in
City and County of San Francisco

Problem

Surface the first and last times each service experienced a connection timeout. Only look at error messages that mention 'timed out', and results should be ordered by service name.

First timeout. Last timeout. Each service.

Tables

err_tracks

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

err_tracks: 10 of 200 rows
err_iderr_typemessagesvc_nameseveritycountfirst_at
137TypeErrorCannot read property of undefinedpayment-apierror82026-02-02 01:11:00
174ConnectionTimeoutConnection timed out after 30suser-svcwarning152026-03-03 02:22:00
211OutOfMemoryHeap space exhaustedsearch-apiinfo222026-04-04 03:33:00
248FileNotFoundFile /tmp/data.csv not foundgatewayFatal292026-05-05 04:44:00
285PermissionDeniedAccess denied for user rootworkerERROR362026-06-06 05:55:00
322RateLimitExceededToo many requests from 10.0.0.1auth-svcfatal432026-07-07 06:06:00
359ParseErrorMalformed JSON in request bodypayment-apierror502026-08-08 07:17:00
396nullpointerexceptionStack overflow in recursive calluser-svcwarning572026-09-09 08:28:00
433TYPEERRORDivision by zerosearch-apiinfo642026-01-10 09:39:00
470NullPointerExceptionUnexpected null value at line 42gatewayFatalNULL2026-02-11 10:50:00

Expected output

First 5 of 6 rows
svc_nameearliest_timeoutlatest_timeout
auth-svc2026-01-17 00:12:002026-07-15 18:42:00
gateway2026-02-27 10:02:002026-08-25 04:32:00
payment-api2026-01-13 12:00:002026-07-11 06:30:00
search-api2026-03-05 08:20:002026-09-03 02:50:00
user-svc2026-03-03 02:22:002026-09-07 14:22:00

Worked solution and explanation

What this is really asking

This is a per-service min/max reduction hiding behind a log search. Anyone can write MIN and MAX; what separates candidates is which rows reach them. The table hands you an err_type of 'ConnectionTimeout' that looks like the obvious filter, but the question is about messages that mention 'timed out', and on this data those two sets overlap on only half the rows. Filter on the label and you report three services instead of six, with bounds taken from rows that never timed out.

Step 1: Filter on the message text

Put message LIKE '%timed out%' in WHERE so only timeout rows reach the aggregate. An exact message = 'timed out' finds nothing, because every message is a full sentence like 'Connection timed out after 30s'.

Step 2: Reduce each service to its two bounds

Use GROUP BY svc_name with MIN(first_at) and MAX(first_at) side by side, so both extremes come out of the same pass, one row per service. first_at is text in 'YYYY-MM-DD HH:MM:SS' form, so string order is time order and no conversion is needed.

Step 3: List services alphabetically

ORDER BY svc_name gives the listing the question asks for. After grouping, each service appears once, so the sort has no ties to break.

The solution

FIRST AND LAST TIMEOUT PER SERVICE
SELECT
  svc_name,
  MIN(first_at) AS earliest_timeout,
  MAX(first_at) AS latest_timeout
FROM err_tracks
WHERE message LIKE '%timed out%'
GROUP BY svc_name
ORDER BY svc_name;
Keyed off the label
WHERE err_type = 'ConnectionTimeout' keeps 20 rows, but only half of them say 'timed out'. The result shrinks to 'auth-svc', 'gateway' and 'user-svc', and their bounds come partly from unrelated errors.
Keyed off the message
WHERE message LIKE '%timed out%' keeps the 20 rows that actually timed out. They are spread over all six services, 'worker' included, and every bound is a real timeout.

Common follow-up questions

Common follow-up questions