Swiggy: Genie Tasks Flagged by Blocked Keywords

Medium
962.

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 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

ColumnType
task_idint
customer_idint
item_notevarchar

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

ColumnType
keywordvarchar
reasonvarchar

Primary key: keyword.

One row per screened word, in lower case.

Example

The rows must come back in the order shown.

GenieTask

task_idcustomer_iditem_note
111House keys and a lunch box
212Return the BEER crate to the shop
313Envelope with Cash and gold coins
414NULL
515Golden retriever food bag
616Laptop charger
717Cashew packet

BlockedKeyword

keywordreason
alcoholexcise rules
beerexcise rules
cashvaluables
cylinderhazardous
goldvaluables

Output

task_idmatched_keywords
2beer
3cash, gold
5gold
7cash
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
task_idint
customer_idint
item_notevarchar

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

ColumnType
keywordvarchar
reasonvarchar

Primary key: keyword.

One row per screened word, in lower case.

Examples

Example 1

GenieTask

task_idcustomer_iditem_note
111House keys and a lunch box
212Return the BEER crate to the shop
313Envelope with Cash and gold coins
414NULL
515Golden retriever food bag
616Laptop charger
717Cashew packet

BlockedKeyword

keywordreason
alcoholexcise rules
beerexcise rules
cashvaluables
cylinderhazardous
goldvaluables

Output

task_idmatched_keywords
2beer
3cash, gold
5gold
7cash

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_id

Another 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_id

Another 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 →