Finding the Topic of Each Post - Problem
You are given two tables: Keywords and Posts.
The Keywords table contains topic IDs and words that express those topics. Multiple words can express the same topic, and one word may be used for multiple topics.
The Posts table contains post IDs and their content (English letters and spaces only).
Task: Find the topic of each post based on these rules:
- If a post contains no keywords from any topic → topic is
"Ambiguous!" - If a post contains keywords from one or more topics → topic is a comma-separated string of topic IDs in ascending order (no duplicates)
- Keyword matching is case insensitive
Table Schema
Keywords
| Column Name | Type | Description |
|---|---|---|
topic_id
PK
|
int | ID of the topic |
word
PK
|
varchar | Keyword that expresses this topic |
Primary Key: (topic_id, word)
Posts
| Column Name | Type | Description |
|---|---|---|
post_id
PK
|
int | Unique identifier for the post |
content
|
varchar | Post content (English letters and spaces only) |
Primary Key: post_id
Input & Output
Example 1 — Posts with Multiple Topic Matches
Input Tables:
Keywords
| topic_id | word |
|---|---|
| 1 | golden |
| 1 | retriever |
| 2 | Puppy |
| 2 | dog |
Posts
| post_id | content |
|---|---|
| 1 | My DOG is a Golden Retriever |
| 2 | A good boi doggy |
| 3 | train transport |
Output:
| post_id | topic |
|---|---|
| 1 | 1,2 |
| 2 | 2 |
| 3 | Ambiguous! |
💡 Note:
Post 1 contains 'DOG' (matches topic 2), 'Golden' and 'Retriever' (both match topic 1) → topics '1,2'. Post 2 contains 'doggy' which doesn't exactly match 'dog' → 'Ambiguous!'. Post 3 has no keyword matches → 'Ambiguous!'.
Example 2 — Case Insensitive Matching
Input Tables:
Keywords
| topic_id | word |
|---|---|
| 1 | code |
| 1 | programming |
| 2 | SQL |
Posts
| post_id | content |
|---|---|
| 1 | Learning SQL and Programming |
| 2 | Writing CODE is fun |
| 3 | Hello world |
Output:
| post_id | topic |
|---|---|
| 1 | 1,2 |
| 2 | 1 |
| 3 | Ambiguous! |
💡 Note:
Case insensitive matching: Post 1 matches 'SQL' (topic 2) and 'Programming' (topic 1). Post 2 matches 'CODE' which matches keyword 'code' (topic 1). Post 3 has no matches.
Constraints
-
1 ≤ topic_id ≤ 1000 -
1 ≤ post_id ≤ 1000 -
wordandcontentconsist of English letters and spaces only -
All words in Keywords table are unique per topic
Visualization
Tap to expand
Understanding the Visualization
1
Input Tables
Posts with content and Keywords with topics
2
Pattern Matching
Case-insensitive LIKE matching between content and keywords
3
Topic Aggregation
Group matching topics per post, handle no-match cases
Key Takeaway
🎯 Key Insight: Use LEFT JOIN with LIKE pattern matching to find all keyword matches, then aggregate topic IDs while handling no-match cases
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code