Post Topic Resolution from Keyword Signals

Hard

A content intelligence platform stores keyword dictionaries for editorial themes and needs to classify each social post into one or more topic IDs. Table: Keywords topic_id: integer topic identifier word: keyword token associated with that topic (topic_id, word) is unique Table: Posts post_id: unique post identifier content: post text containing only English letters and spaces Classification rule: A topic matches a post if at least one of its keywords appears in the post content as a full token. Matching is case-insensitive. Matching is token-based, not generic substring search. Examples of token behavior: keyword = "war" matches token "war" keyword = "war" does not match token "warning" Task: For each post, return its topic label using these output rules: If no topic matches, return "Ambiguous!". Otherwise, return topic IDs in ascending numeric order, comma-separated, without duplicates. Output requirements: Return exactly these columns in this order: post_id topic Return one row per post. Row ordering is not important. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Keywords:
| topic_id | word     |
|----------|----------|
| 1        | handball |
| 1        | football |
| 2        | vaccine  |
| 3        | war      |

Posts:
| post_id | content                                                 |
|---------|---------------------------------------------------------|
| 1       | Fans in Europe still call it football                   |
| 2       | The handball final and football semifinal sold out      |
| 3       | Please stop the war and play handball                   |
| 4       | Morning walk with flowers and coffee                    |

Output

[
  {"post_id":1,"topic":"1"},
  {"post_id":2,"topic":"1"},
  {"post_id":3,"topic":"1,3"},
  {"post_id":4,"topic":"Ambiguous!"}
]

Explanation

Post 3 matches two topics because it contains both war (topic 3) and handball (topic 1). Post 4 has no exact keyword token.

Example 2

Input

Keywords:
| topic_id | word     |
|----------|----------|
| 10       | cloud    |
| 11       | cloud    |
| 11       | security |
| 12       | retail   |

Posts:
| post_id | content                               |
|---------|---------------------------------------|
| 101     | Cloud migration and security review   |
| 102     | Retail campaign planning for stores   |
| 103     | Cloudy weather outlook                |

Output

[
  {"post_id":101,"topic":"10,11"},
  {"post_id":102,"topic":"12"},
  {"post_id":103,"topic":"Ambiguous!"}
]

Explanation

The token cloud belongs to both topics 10 and 11. Cloudy is a different token and does not match cloud.

Example 3

Input

Keywords:
| topic_id | word     |
|----------|----------|
| 2        | data     |
| 2        | pipeline |
| 4        | scale    |
| 7        | risk     |

Posts:
| post_id | content                          |
|---------|----------------------------------|
| 201     | DATA data data pipeline          |
| 202     | Scalable systems demand rigor    |
| 203     | Risk data pipeline for scale     |

Output

[
  {"post_id":201,"topic":"2"},
  {"post_id":202,"topic":"Ambiguous!"},
  {"post_id":203,"topic":"2,4,7"}
]

Explanation

Topic IDs are deduplicated and sorted. Repeated keyword occurrences do not duplicate topic IDs.

Constraints

0 <= number of rows in Keywords <= 5000
0 <= number of rows in Posts <= 5000
1 <= topic_id <= 1000000000
1 <= post_id <= 1000000000
word contains only English letters
content contains only English letters and spaces
(topic_id, word) pairs are unique
A keyword match is case-insensitive and token-based
Do not duplicate topic IDs in the output topic string
Sort topic IDs in ascending numeric order before joining by commas
Return exactly two columns: post_id, topic
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: post_id, topic

Keywords: | topic_id | word | |----------|----------| | 1 | handball | | 1 | football | | 2 | vaccine | | 3 | war | Posts: | post_id | content | |---------|---------------------------------------------------------| | 1 | Fans in Europe still call it football | | 2 | The handball final and football semifinal sold out | | 3 | Please stop the war and play handball | | 4 | Morning walk with flowers and coffee |

Keywords: | topic_id | word | |----------|----------| | 10 | cloud | | 11 | cloud | | 11 | security | | 12 | retail | Posts: | post_id | content | |---------|---------------------------------------| | 101 | Cloud migration and security review | | 102 | Retail campaign planning for stores | | 103 | Cloudy weather outlook |

Keywords: | topic_id | word | |----------|----------| | 2 | data | | 2 | pipeline | | 4 | scale | | 7 | risk | Posts: | post_id | content | |---------|----------------------------------| | 201 | DATA data data pipeline | | 202 | Scalable systems demand rigor | | 203 | Risk data pipeline for scale |