Messages From Specific Users
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
The trust and safety team received a complaint about users 2 and 3. Pull all chat messages where either user sent the message, or where the content mentions their user IDs.
Specific users. What did they say?
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
| msg_id | channel | sender_id | content | msg_type | sent_at | edited | reply_to |
|---|---|---|---|---|---|---|---|
| 147 | #engineering | 197 | Anyone seeing increased latency? | file | 2026-02-02 01:07:00 | 0 | NULL |
| 194 | #incidents | 294 | PR #412 ready for review | reaction | 2026-03-03 02:14:00 | 0 | NULL |
| 241 | #random | 391 | Dashboard is down | thread_reply | 2026-04-04 03:21:00 | 0 | NULL |
| 288 | #deployments | 488 | Fixed the flaky test | system | 2026-05-05 04:28:00 | 0 | NULL |
| 335 | #data-team | 585 | Merged the migration | Text | 2026-06-06 05:35:00 | 0 | NULL |
| 382 | #general | 682 | NULL | FILE | 2026-07-07 06:42:00 | 0 | NULL |
| 429 | #engineering | 779 | LGTM | text | 2026-08-08 07:49:00 | 0 | NULL |
| 476 | #incidents | 876 | 👍 | file | 2026-09-09 08:56:00 | 0 | 5 |
| 523 | #random | 973 | reaction | 2026-01-10 09:03:00 | 0 | NULL | |
| 570 | #deployments | 1070 | Can someone check the pipeline? | thread_reply | 2026-02-11 10:10:00 | 0 | NULL |
Expected output
| msg_id | channel | sender_id | content | msg_type | sent_at | edited | reply_to |
|---|---|---|---|---|---|---|---|
| 194 | #incidents | 294 | PR #412 ready for review | reaction | 2026-03-03 02:14:00 | 0 | NULL |
| 617 | #data-team | 1167 | Rolling back to v2.0 | system | 2026-03-12 11:17:00 | 0 | NULL |
| 664 | #general | 1264 | Deploying v2.1 to prod | Text | 2026-04-13 12:24:00 | 1 | 11 |
| 758 | #incidents | 1458 | PR #412 ready for review | text | 2026-06-15 14:38:00 | 0 | NULL |
| 1181 | #data-team | 2331 | Rolling back to v2.0 | reaction | 2026-06-24 23:41:00 | 0 | NULL |
Worked solution and explanation
Why this problem exists in real interviews
The chat_msgs table contains channel and content values that must be processed with pattern matching. This appears as a fundamentals check to probe whether you reason about the correct aggregation grain before writing any window or GROUP BY clause. Getting the column references right on the first try is what interviewers watch for.
Break down the requirements
Step 1: Read from chat_msgs
The query targets chat_msgs with 8 columns. Identify which columns are needed for the output.
Step 2: Filter to the target rows
Apply the LIKE pattern match in the WHERE clause. This narrows the dataset before any grouping or aggregation.
Step 3: Return the result set
Select the required columns with any necessary aliasing or formatting.
The solution
SELECT msg_id, channel, sender_id, content, msg_type, sent_at, edited, reply_to
FROM chat_msgs
WHERE sender_id IN (2, 3)
OR content LIKE '%2%'
OR content LIKE '%3%'Common follow-up questions
- What happens to your result if `chat_msgs`.reply_to contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on `reply_to`.)
- How would you verify that your aggregation on `chat_msgs`.msg_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
- With millions of distinct values in `chat_msgs`.msg_id, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `msg_id`.)