Top Pattern Matches

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

L4

Problem

In our log messages, a reference number is any three characters, a dash, then at least four more characters, and it can sit anywhere in the text: at the start, in the middle or at the end of a message. Using server_logs, find the 10 servers with the most messages that contain one, along with each server's match count, most matches first and alphabetical by server name when counts tie.

A needle in a haystack, but how many haystacks?

Tables

server_logs

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

Expected output

Worked solution and explanation

What this is really asking

This is a positional pattern match dressed up as log forensics. Anyone can write the GROUP BY and the LIMIT. What separates candidates is pinning the shape: three characters, a literal dash, then four or more characters, anywhere in the text. Reach for %-% and every hyphenated message counts. Per-server totals balloon, and the top 10 comes back with the wrong servers and the wrong counts.

Break down the requirements

Step 1: Match the shape with LIKE

%___-____% reads as: anything, exactly 3 characters, a dash, 4 characters, anything. Each _ holds one position. The trailing % is what lets the run after the dash be longer than 4, and the leading % lets the reference sit mid-message.

Step 2: Count matching messages per server

GROUP BY server_name with COUNT(*). The WHERE filters before grouping, so the count is messages that contain a reference, not total references.

Step 3: Keep the top 10, ties by name

ORDER BY match_count DESC, server_name then LIMIT 10. The name key decides which server survives when two counts tie at the cut, so the top 10 is the same set every run.

The solution

SELECT server_name, COUNT(*) AS match_count
FROM server_logs
WHERE message LIKE '%___-____%'
GROUP BY server_name
ORDER BY match_count DESC, server_name
LIMIT 10

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• What changes if the reference must be digits only? (Switch to `REGEXP_LIKE(message, '[0-9]{3}-[0-9]{4,}')`. Slower, and it rejects `abc-defg`.)
• What if you wanted total occurrences, not messages? (Use `REGEXP_COUNT` and `SUM()` per server. `COUNT(*)` undercounts messages with several references.)
• How do you exploit the `log_timestamp` partition? (Add `WHERE log_timestamp >= ...` so the planner prunes partitions. Without it, every partition is scanned regardless of the `LIKE`.)