Finding the Topic of Each Post - Practice Coding Problems

Finding the Topic of Each Post - Problem

Database Hard

Finding the Topic of Each Post

Imagine you're building a content management system for a social media platform! 📱 You have a collection of posts and need to automatically categorize them based on their content.

The Challenge:
Given two tables - Keywords (which maps topics to their associated words) and Posts (containing post content), you need to determine which topics each post belongs to.

Rules for Topic Assignment:
• If a post contains any keyword from a topic (case-insensitive), that post belongs to that topic
• If a post has no matching keywords, mark it as "Ambiguous!"
• If a post matches multiple topics, list all topic IDs sorted in ascending order, separated by commas
• Remove duplicate topic IDs from the result

Input:
• Keywords table: Maps topic_id to words that represent that topic
• Posts table: Contains post_id and content (English letters and spaces only)

Output:
Return each post_id with its corresponding topic string (either topic IDs or "Ambiguous!").

Input & Output

example_1.sql — Basic Topic Matching

$ Input: Keywords table: +----------+--------+ | topic_id | word | +----------+--------+ | 1 | golden | | 1 | yellow | | 2 | code | | 2 | java | +----------+--------+ Posts table: +---------+-----------------------------------+ | post_id | content | +---------+-----------------------------------+ | 1 | We have golden opportunity ahead | | 2 | I love java code very much | | 3 | blue sky and white clouds | +---------+-----------------------------------+

› Output: +---------+------------+ | post_id | topic | +---------+------------+ | 1 | 1 | | 2 | 2 | | 3 | Ambiguous! | +---------+------------+

💡 Note: Post 1 contains 'golden' which belongs to topic 1. Post 2 contains both 'java' and 'code' which both belong to topic 2. Post 3 doesn't contain any keywords, so it's marked as 'Ambiguous!'.

example_2.sql — Multiple Topic Matching

$ Input: Keywords table: +----------+--------+ | topic_id | word | +----------+--------+ | 1 | golden | | 2 | code | | 3 | sql | +----------+--------+ Posts table: +---------+---------------------------+ | post_id | content | +---------+---------------------------+ | 1 | golden code is beautiful | | 2 | learn sql and code today | +---------+---------------------------+

› Output: +---------+-------+ | post_id | topic | +---------+-------+ | 1 | 1,2 | | 2 | 2,3 | +---------+-------+

💡 Note: Post 1 contains 'golden' (topic 1) and 'code' (topic 2), so topics are '1,2'. Post 2 contains 'sql' (topic 3) and 'code' (topic 2), resulting in '2,3' (sorted order).

example_3.sql — Case Insensitive Matching

$ Input: Keywords table: +----------+--------+ | topic_id | word | +----------+--------+ | 1 | Python | | 2 | JAVA | +----------+--------+ Posts table: +---------+------------------------+ | post_id | content | +---------+------------------------+ | 1 | I love python coding | | 2 | java is great | | 3 | Learning c++ today | +---------+------------------------+

› Output: +---------+------------+ | post_id | topic | +---------+------------+ | 1 | 1 | | 2 | 2 | | 3 | Ambiguous! | +---------+------------+

💡 Note: Case insensitive matching: 'python' matches 'Python' (topic 1), 'java' matches 'JAVA' (topic 2). Post 3 has no matching keywords.

Visualization

Tap to expand

Understanding the Visualization

Keyword Database

System maintains a database of topics and their associated keywords (like 'technology' → ['code', 'java', 'python'])

Content Analysis

For each new post, scan the content for any keywords that appear in our database

Topic Matching

If keywords are found, collect all corresponding topic IDs and sort them

Result Generation

Output the sorted topic list or 'Ambiguous!' if no keywords match

Key Takeaway

🎯 Key Insight: Use SQL's LEFT JOIN with LIKE pattern matching for efficient keyword detection, combined with conditional aggregation to handle both matching and non-matching cases in a single query.

Time & Space Complexity

Time Complexity

⏱️

O(P × K × log K)

P posts × K keywords with efficient string matching and sorting

⚡ Linearithmic

Space Complexity

O(P × T)

P posts × T average topics per post for result storage

✓ Linear Space

Constraints

1 ≤ number of keywords ≤ 100
1 ≤ number of posts ≤ 100
1 ≤ topic_id ≤ 100
1 ≤ word.length ≤ 30
1 ≤ content.length ≤ 300
word and content consist of English letters and spaces only
All words are case insensitive for matching

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses LEFT JOIN with LIKE pattern matching to efficiently find keyword matches in post content. Key techniques include case-insensitive string matching using LOWER(), GROUP BY with conditional aggregation to handle the 'Ambiguous!' case, and DISTINCT with ORDER BY to ensure unique, sorted topic IDs.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Cross Join with String Search)	O(P × K × L)	O(P × K)	Cross join all posts with all keywords and check each combination manually
Optimized JOIN with String Functions (Optimal)	O(P × K × log K)	O(P × T)	Use efficient SQL JOINs with LIKE operator for pattern matching

Brute Force (Cross Join with String Search) — Algorithm Steps

Cross join Posts table with Keywords table
For each post-keyword pair, check if keyword exists in content (case-insensitive)
Group results by post_id and collect matching topic_ids
Handle posts with no matches by assigning 'Ambiguous!'
Sort topic_ids and concatenate with commas

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create all possible post-keyword combinations

String Match

Check if keyword exists in post content

Filter Matches

Keep only matching combinations

Group & Aggregate

Group by post and collect topic IDs

Code -

solution.c — C

// SQL solution for finding topics of posts - Brute Force approach

#include <stdio.h>
#include <stdlib.h>

char* solution() {
    static char sqlQuery[] = 
        "SELECT "
        "    p.post_id, "
        "    CASE "
        "        WHEN COUNT(DISTINCT k.topic_id) = 0 THEN 'Ambiguous!' "
        "        ELSE GROUP_CONCAT(DISTINCT k.topic_id ORDER BY k.topic_id SEPARATOR ',') "
        "    END as topic "
        "FROM Posts p "
        "LEFT JOIN ( "
        "    SELECT DISTINCT p2.post_id, k2.topic_id "
        "    FROM Posts p2 "
        "    CROSS JOIN Keywords k2 "
        "    WHERE LOWER(p2.content) LIKE CONCAT('%', LOWER(k2.word), '%') "
        ") matched ON p.post_id = matched.post_id "
        "LEFT JOIN Keywords k ON matched.topic_id = k.topic_id "
        "GROUP BY p.post_id "
        "ORDER BY p.post_id";
    return sqlQuery;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(P × K × L)

P posts × K keywords × L average content length for string matching

✓ Linear Growth

Space Complexity

O(P × K)

Stores all post-keyword combinations in memory

✓ Linear Space

Constraints

1 ≤ number of keywords ≤ 100
1 ≤ number of posts ≤ 100
1 ≤ topic_id ≤ 100
1 ≤ word.length ≤ 30
1 ≤ content.length ≤ 300
word and content consist of English letters and spaces only
All words are case insensitive for matching

28.8K Views

Medium-High Frequency

~25 min Avg. Time

1.2K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Cross Join with String Search) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler