Swiggy: Genie Tasks Flagged by Blocked Keywords
Genie will not carry some items — alcohol, cash, valuables. Customers describe the parcel in a free-text note, and the trust-and-safety team screens notes…
- Difficulty: Medium
- Topics: Strings, Joins, Aggregation
- Dialect: MySQL
- Problem: #962
Problem statement
Genie will not carry some items — alcohol, cash, valuables. Customers describe the parcel in a free-text note, and the trust-and-safety team screens notes against a list of blocked keywords, sending matches to a human reviewer.
A note matches a keyword when the keyword appears anywhere inside it as a substring, ignoring letter case (cash matches Cash envelope and also Cashew packet — the reviewer sorts out false alarms). Keywords are stored in lower case. Return task_id and matched_keywords, the distinct keywords the note matched in alphabetical order joined by ', ', for every task whose note matched at least one. Tasks with no note (NULL) match nothing. Order the rows by task_id.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: GenieTask
| Column | Type |
|---|---|
| task_id | int |
| customer_id | int |
| item_note | varchar |
Primary key: task_id.
One row per pick-up-and-drop request; item_note is what the customer typed about the parcel, or NULL.
Table: BlockedKeyword
| Column | Type |
|---|---|
| keyword | varchar |
| reason | varchar |
Primary key: keyword.
One row per screened word, in lower case.
Examples
Example 1
GenieTask
| task_id | customer_id | item_note |
|---|---|---|
| 1 | 11 | House keys and a lunch box |
| 2 | 12 | Return the BEER crate to the shop |
| 3 | 13 | Envelope with Cash and gold coins |
| 4 | 14 | NULL |
| 5 | 15 | Golden retriever food bag |
| 6 | 16 | Laptop charger |
| 7 | 17 | Cashew packet |
BlockedKeyword
| keyword | reason |
|---|---|
| alcohol | excise rules |
| beer | excise rules |
| cash | valuables |
| cylinder | hazardous |
| gold | valuables |
Output
| task_id | matched_keywords |
|---|---|
| 2 | beer |
| 3 | cash, gold |
| 5 | gold |
| 7 | cash |
How to solve Swiggy: Genie Tasks Flagged by Blocked Keywords
The keyword list is data, so the matching is a join on a pattern rather than a fixed WHERE clause: each task is paired with each keyword, and the pair survives when the keyword occurs inside the note. LIKE CONCAT('%', k.keyword, '%') builds the substring pattern from the column. Lower-casing the note first makes the test independent of case on every database — MySQL's default collation would ignore case anyway, but other engines and binary collations would not, and the keywords are already stored in lower case.
After the join a task appears once per keyword it matched, so grouping by task and GROUP_CONCAT(… ORDER BY keyword SEPARATOR ', ') produces the list; the explicit order makes the string deterministic. A task that matched nothing has no joined rows and is absent, and a NULL note makes LIKE NULL, which also matches nothing.
LOCATE(needle, haystack) and INSTR(haystack, needle) return the position of a substring (0 when absent) and are equivalent tests that avoid building a pattern — handy if a keyword could ever contain % or _, which LIKE would treat as wildcards. The cost is tasks × keywords comparisons; for a long keyword list a full-text index is the production answer.
Reference solution (MySQL)
SELECT t.task_id,
GROUP_CONCAT(DISTINCT k.keyword ORDER BY k.keyword SEPARATOR ', ') AS matched_keywords
FROM GenieTask t
JOIN BlockedKeyword k ON LOWER(t.item_note) LIKE CONCAT('%', k.keyword, '%')
GROUP BY t.task_id
ORDER BY t.task_idAnother way
SELECT t.task_id, GROUP_CONCAT(k.keyword ORDER BY k.keyword SEPARATOR ', ') AS matched_keywords FROM GenieTask t, BlockedKeyword k WHERE LOCATE(k.keyword, LOWER(t.item_note)) > 0 GROUP BY t.task_id ORDER BY t.task_idAnother way
SELECT t.task_id, GROUP_CONCAT(k.keyword ORDER BY k.keyword SEPARATOR ', ') AS matched_keywords FROM GenieTask t JOIN BlockedKeyword k ON INSTR(LOWER(t.item_note), k.keyword) > 0 GROUP BY t.task_id ORDER BY t.task_id← Swiggy: Food Customers Who Later Tried Instamart · Swiggy: Busiest Instamart Store per City Each Month →