SQL: count files where bull and bear appear as standalone space-delimited words

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

Output Contract

Write one read-only PostgreSQL query (a single SELECT; CTEs are allowed) that returns exactly two rows with these columns:

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:

Expected output:

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.