Recurring Error Types

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

Meta

Problem

To the on-call SRE triaging the payment-api outage, an error message with three or fewer entries in the log is background noise. List the messages in the err_tracks log that rose above that noise for payment-api.

The same errors, recurring.

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 really asks

This is a scoped frequency count dressed up as incident triage. Scoping to 'payment-api' is a plain row filter, and everyone writes it. What separates candidates is where the bar of three sits and what it counts: it is a threshold on log entries per message, which only exists after grouping, so it belongs in HAVING. Put it in WHERE and the query errors. Reach for SUM(count) because the table has a column called count and all ten payment-api messages clear the bar, burying the four that genuinely repeat. Skip the threshold and you get all ten too, because every payment-api message has at least three entries.

Building the query

Step 1: Scope to the one service

WHERE svc_name = 'payment-api' discards every other service's rows before any counting happens. This is a row-level filter, exactly what WHERE is for.

Step 2: Count entries per message

GROUP BY message collapses the survivors into one group per message, and COUNT(*) gives how many log entries payment-api wrote for each. An entry is a row, so you count rows, not the stored count values.

Step 3: Keep only the recurring ones

HAVING COUNT(*) > 3 runs after the grouping and drops any message with three or fewer entries. WHERE cannot do this: the per-message count does not exist yet when WHERE is evaluated.

The solution

SELECT message
FROM err_tracks
WHERE svc_name = 'payment-api'
GROUP BY message
HAVING COUNT(*) > 3

Summing the stored tally

HAVING SUM(count) > 3 adds up the count column, which holds values like 8 and 50 per row, so all ten payment-api messages sail past three.

Counting log entries

HAVING COUNT(*) > 3 counts rows: four messages have four entries and survive, six have three and drop.

Common follow-up questions

• How would you return each message's number of entries next to `message`? (Tests moving from a filtered key list to reporting the aggregate itself.)
• What changes if the SRE wants this for every service at once, one threshold applied per service? (Probes grouping over `svc_name, message` rather than hard-coding a single service.)
• What would you have to do to report `err_type` instead, given 'TypeError' and 'TYPEERROR' both appear? (Tests normalizing a dirty key before grouping.)