Messages Containing Keyword

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

Google

Problem

The on-call team is searching chat history for latency-related discussions during a recent incident. Pull all fields for messages that contain the word 'latency'.

Flagged terms in the messages.

Tables

chat_msgs

Columns: msg_id INTEGER, channel TEXT, sender_id INTEGER, content TEXT, msg_type TEXT, sent_at TEXT, edited INTEGER, reply_to INTEGER

Expected output

Worked solution and explanation

This is a substring search dressed up as incident forensics. Nobody posts 'latency' as a whole message: on this table it only ever appears inside a sentence, 'Anyone seeing increased latency?', so the skill is finding a word anywhere inside free text. The trap is reading 'contain the word' as equality. content = 'latency', or an anchored LIKE 'latency%', runs cleanly and returns zero rows. The on-call team then concludes nobody talked about latency during the incident, and the postmortem loses all 18 real messages.

Getting to the query

Step 1: Search content, and ignore msg_type

The column that jumps out is msg_type, which holds 'file', 'FILE', 'Text' and 'text'. That messiness invites cleanup, but it has nothing to do with the question: the word lives in content. Normalizing msg_type here is effort spent on the wrong field.

Step 2: Put a wildcard on both sides

'%latency%' matches the word at the start, the middle or the end of a message. 'latency%' only matches messages that open with it. '%latency' only matches messages that end with it, and the real message ends in a question mark, so even that misses every row.

Step 3: Return all eight columns by name

The ask is every field, untouched: no counting and no reshaping. List the eight columns explicitly, so the output contract is visible in the query and survives a schema change.

SELECT msg_id, channel, sender_id, content, msg_type, sent_at, edited, reply_to
FROM chat_msgs
WHERE content LIKE '%latency%'

Exact match

WHERE content = 'latency' compares the whole value. No message on the table is the bare word, so the result is empty and looks like a clean answer.

Substring match

WHERE content LIKE '%latency%' finds the word anywhere in the sentence: 18 messages, every one of them 'Anyone seeing increased latency?'.

Common follow-up questions

• The incident ran for two hours on one day. How do you restrict the search to that window, and what does it do to the scan? (Tests partition pruning on `sent_at` ahead of the expensive substring filter.)
• How would you stop 'latency' from matching inside a longer token like 'latencyBudget'? (Tests word-boundary matching with a regular expression instead of a bare wildcard.)
• How would you also pull every reply to the latency messages? (Tests relating `chat_msgs` to itself through `reply_to`.)