The Words That Page Us

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Yelp

Problem

During an incident review, on-call wants every chat message mentioning 'latency', 'down', 'back', or 'pipeline' lined up against the error tracking entries logged in the same month. Show the channel, the message text, and the severity of the error it matched.

Certain words trigger an investigation.

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

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

Why this problem exists in real interviews

Strip the incident-review costume and this is a derived-string equi-join hiding a many-to-many explosion. The only thing tying a chat message to an error is the calendar month you carve out of two timestamp strings, so SUBSTR(sent_at, 1, 7) isn't formatting, it IS the join key. Anyone can write the OR-chain keyword filter; what separates candidates is naming, out loud, that equal months pair every matching message with every same-month error before they hit run. Miss it and the result balloons past 100M rows per month bucket while partition pruning quietly dies, because the planner cannot push a SUBSTR expression down to the partition column.

Break down the requirements

Step 1: Filter messages by keyword

OR-chain of cm.content LIKE on '%latency%', '%down%', '%back%', '%pipeline%'. Leading wildcards mean no btree index helps. Wrap in LOWER() if the engine is case-sensitive.

Step 2: Define same-month

SUBSTR(cm.sent_at, 1, 7) = SUBSTR(et.first_at, 1, 7) gives 'YYYY-MM'. Works on ISO strings or timestamps cast to text. Both sides are derived, so neither partition key gets pruned.

Step 3: Project the three columns

cm.channel, cm.content, et.severity. No aggregation in the spec, so duplicates from the many-to-many join land in the result set.

Step 4: Decide on shape

One chat row pairs with every same-month error. Confirm with the interviewer: per-message-per-error, or DISTINCT on (channel, content, severity), or one row per message with max severity.

The solution

SELECT cm.channel, cm.content, et.severity
FROM chat_msgs cm
INNER JOIN err_tracks et
  ON SUBSTR(cm.sent_at, 1, 7) = SUBSTR(et.first_at, 1, 7)
WHERE cm.content LIKE '%latency%'
   OR cm.content LIKE '%down%'
   OR cm.content LIKE '%back%'
   OR cm.content LIKE '%pipeline%'

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you collapse to one row per message with the highest severity? (Tests window functions or GROUP BY with a severity rank.)
• Rewrite the month join so partition pruning actually fires. (Probes whether you reach for `DATE_TRUNC('month', ...)` and range predicates instead of `SUBSTR`.)
• How do you make the keyword filter case-insensitive across Postgres and Snowflake? (Checks awareness of `LOWER()` vs `ILIKE` portability.)
• Would a tokenized full-text index change your approach at 60M rows? (Opens the door to GIN, search services, or precomputed keyword flags.)