-
Notifications
You must be signed in to change notification settings - Fork 8
/
2199. Finding the Topic of Each Post.py
144 lines (95 loc) · 4.15 KB
/
2199. Finding the Topic of Each Post.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
2199. Finding the Topic of Each Post
Hard
16
9
Add to List
Share
SQL Schema
Table: Keywords
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| topic_id | int |
| word | varchar |
+-------------+---------+
(topic_id, word) is the primary key for this table.
Each row of this table contains the id of a topic and a word that is used to express this topic.
There may be more than one word to express the same topic and one word may be used to express multiple topics.
Table: Posts
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| post_id | int |
| content | varchar |
+-------------+---------+
post_id is the primary key for this table.
Each row of this table contains the ID of a post and its content.
Content will consist only of English letters and spaces.
Leetcode has collected some posts from its social media website and is interested in finding the topics of each post. Each topic can be expressed by one or more keywords. If a keyword of a certain topic exists in the content of a post (case insensitive) then the post has this topic.
Write an SQL query to find the topics of each post according to the following rules:
If the post does not have keywords from any topic, its topic should be "Ambiguous!".
If the post has at least one keyword of any topic, its topic should be a string of the IDs of its topics sorted in ascending order and separated by commas ','. The string should not contain duplicate IDs.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Keywords table:
+----------+----------+
| topic_id | word |
+----------+----------+
| 1 | handball |
| 1 | football |
| 3 | WAR |
| 2 | Vaccine |
+----------+----------+
Posts table:
+---------+------------------------------------------------------------------------+
| post_id | content |
+---------+------------------------------------------------------------------------+
| 1 | We call it soccer They call it football hahaha |
| 2 | Americans prefer basketball while Europeans love handball and football |
| 3 | stop the war and play handball |
| 4 | warning I planted some flowers this morning and then got vaccinated |
+---------+------------------------------------------------------------------------+
Output:
+---------+------------+
| post_id | topic |
+---------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1,3 |
| 4 | Ambiguous! |
+---------+------------+
Explanation:
1: "We call it soccer They call it football hahaha"
"football" expresses topic 1. There is no other word that expresses any other topic.
2: "Americans prefer basketball while Europeans love handball and football"
"handball" expresses topic 1. "football" expresses topic 1.
There is no other word that expresses any other topic.
3: "stop the war and play handball"
"war" expresses topic 3. "handball" expresses topic 1.
There is no other word that expresses any other topic.
4: "warning I planted some flowers this morning and then got vaccinated"
There is no word in this sentence that expresses any topic. Note that "warning" is different from "war" although they have a common prefix.
This post is ambiguous.
Note that it is okay to have one word that expresses more than one topic.
/* Write your T-SQL query statement below */
WITH cte_topic AS (
SELECT DISTINCT
posts.post_id,
topic_id
FROM
posts
LEFT JOIN keywords
ON posts.content LIKE '% ' + keywords.word + ' %' -- Match if in middle of sentence
OR posts.content LIKE keywords.word + ' %' -- Match if in start of sentence
OR posts.content LIKE '% ' + keywords.word -- Match if in end of sentence
OR posts.content LIKE keywords.word -- Match if only word
)
SELECT
post_id,
ISNULL(STRING_AGG(topic_id,','),'Ambiguous!') AS topic
FROM
cte_topic
GROUP BY
post_id