SQL: count files where bull and bear appear as standalone space-delimited words
Company: Microsoft
Role: Site Reliability Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
A document store keeps one row per file together with the file's full text. For each of the two words `bull` and `bear`, report how many files contain that word as a standalone word at least once.
### Input Table(s)
Table `files`
| Column | Type | Meaning |
|---|---|---|
| `file_name` | VARCHAR | Name of the file; primary key, never NULL |
| `content` | TEXT | Full text of the file; may be NULL or empty |
### Output Contract
Write one read-only PostgreSQL query (a single SELECT; CTEs are allowed) that returns exactly two rows with these columns:
| Column | Type | Meaning |
|---|---|---|
| `word` | TEXT | Either `bear` or `bull` |
| `file_count` | BIGINT | Number of distinct files whose `content` contains the word as a standalone word |
Order the rows by `word` ascending, so `bear` comes first. Both rows are always present; a word that no file contains has `file_count` equal to 0.
A file contains a word as a standalone word exactly when its content includes the substring made of one space character, the word, and one more space character. Consequently:
- an occurrence at the very start or the very end of the content does not count;
- an occurrence touching punctuation, a tab or a newline does not count (for example `bull.` or `bear,`);
- longer words that contain the word do not count (for example `bullet` or `bears`);
- matching is case-sensitive, so `Bull` does not count.
### Example
Input `files`:
| file_name | content |
|---|---|
| a.txt | traders expect a bull run and a bull rally |
| b.txt | the bear case and the bull case differ |
| c.txt | bullish notes, not a bear. |
| d.txt | bear markets end with a bull |
| e.txt | NULL |
| f.txt | a Bull walked in |
Expected output:
| word | file_count |
|---|---|
| bear | 1 |
| bull | 2 |
`a.txt` counts once for `bull` even though the word appears twice, and `b.txt` counts for both words. `c.txt` and `d.txt` count for neither word (start of text, end of text, attached punctuation, a longer word), `e.txt` has no content, and `f.txt` uses a capital letter.
### Constraints and Clarifications
- `file_name` is unique, so there are no duplicate files; each file adds at most 1 to each word's count no matter how many times the word appears in it.
- NULL content and empty content contain no words.
- Only the ordinary space character (U+0020) acts as a separator.
- If the table is empty, the result still has both rows, each with `file_count` 0.
- There are no ordering ties, because `word` is unique in the output.
```hint Keep both rows
Make sure your query still produces a row for a word that appears in no file at all.
```
Overview: SQL practice question: given a table of files and their text content, count how many files contain the words bull and bear as standalone, space-delimited words. It tests careful pattern matching, case sensitivity, NULL handling, and returning a row for each word even when its count is zero.
Read the full Microsoft Site Reliability Engineer interview experience this question came from