Standalone Market Term File Coverage

Medium

A market intelligence system stores plain-text analyst notes in one table: Table: Files file_name: unique file identifier content: full text content of that file You must produce a two-row keyword coverage report for the words: bull bear Coverage rule: A file contributes to a keyword count if its content contains that keyword at least once as a standalone token. In this task, standalone is defined strictly as a literal-space match: " bull " " bear " Matching is case-sensitive (for example, "BULL" does not count as "bull"). Implications of the rule: Do not count words attached to punctuation or boundaries without spaces. Examples that do not qualify: "bull.", "bear", at start/end with no surrounding spaces, "bullet", "bears". If a file contains a keyword multiple times, it still contributes only 1 for that keyword. Task: Return exactly two rows with columns: word count Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Files:
| file_name   | content                                                                                               |
|-------------|-------------------------------------------------------------------------------------------------------|
| report1.txt | Weekly desk note says the market may enter a bull phase soon.                                        |
| report2.txt | Strategy update says a bull setup exists now, but a bear scenario remains possible next quarter.     |
| report3.txt | Risk memo says a bear setup is likely if rates stay elevated.                                         |

Output

[
  {"word":"bull","count":2},
  {"word":"bear","count":2}
]

Explanation

report1 and report2 contain ' bull '. report2 and report3 contain ' bear '.

Example 2

Input

Files:
| file_name   | content                                                |
|-------------|--------------------------------------------------------|
| a.txt       | bullet points mention bears and bull.                  |
| b.txt       | macro team expects a bear rebound over six weeks       |
| c.txt       | alpha bull beta bull gamma                             |

Output

[
  {"word":"bull","count":1},
  {"word":"bear","count":1}
]

Explanation

a.txt has only false positives. b.txt contributes to bear once. c.txt contributes to bull once, even with repeated appearances.

Example 3

Input

Files:
| file_name | content |
|-----------|---------|

Output

[
  {"word":"bull","count":0},
  {"word":"bear","count":0}
]

Explanation

With no files, both keyword counts are zero.

Constraints

0 <= number of rows in Files <= 350
1 <= length of file_name <= 120
0 <= length of content <= 2000
file_name values are unique in Files
Count each file at most once per keyword
Standalone matching is based on literal-space patterns: ' bull ' and ' bear '
Matching is case-sensitive for both keywords
Return exactly columns: word, count
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: word, count

Files: | file_name | content | |-------------|-------------------------------------------------------------------------------------------------------| | report1.txt | Weekly desk note says the market may enter a bull phase soon. | | report2.txt | Strategy update says a bull setup exists now, but a bear scenario remains possible next quarter. | | report3.txt | Risk memo says a bear setup is likely if rates stay elevated. |

Files: | file_name | content | |-------------|--------------------------------------------------------| | a.txt | bullet points mention bears and bull. | | b.txt | macro team expects a bear rebound over six weeks | | c.txt | alpha bull beta bull gamma |