Amazon: Search Queries Mentioning Each Brand — SQL Problem
The search relevance team is sizing branded demand: how often shoppers type a brand's name into the search box.
- Difficulty: Easy
- Topics: Strings, Joins, Aggregation
- Dialect: MySQL
- Problem: #574
Problem statement
The search relevance team is sizing branded demand: how often shoppers type a brand's name into the search box.
For every brand that appears in at least one query, return brand_name and searches, the number of search queries whose query_text contains the brand name anywhere (as a substring). A query that names two brands counts once for each of them. Brands no query mentions are left out. All text is stored in lower case. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: Brand
| Column | Type |
|---|---|
| brand_id | int |
| brand_name | varchar |
Primary key: brand_id.
One row per brand sold on the store; names are lower case and contain only letters.
Table: SearchQuery
| Column | Type |
|---|---|
| query_id | int |
| query_text | varchar |
| searched_on | date |
Primary key: query_id.
One row per search typed by a shopper, lower-cased.
Examples
Example 1
Brand
| brand_id | brand_name |
|---|---|
| 1 | echo |
| 2 | boat |
| 3 | kindle |
| 4 | prestige |
| 5 | puma |
SearchQuery
| query_id | query_text | searched_on |
|---|---|---|
| 101 | echo dot 5th gen | 2025-03-01 |
| 102 | boat airdopes 141 | 2025-03-01 |
| 103 | kindle paperwhite cover | 2025-03-02 |
| 104 | echo show vs kindle for kids | 2025-03-02 |
| 105 | usb c fast charger | 2025-03-03 |
| 106 | boat smartwatch | 2025-03-03 |
| 107 | echo | 2025-03-04 |
Output
| brand_name | searches |
|---|---|
| echo | 3 |
| boat | 2 |
| kindle | 2 |
How to solve Amazon: Search Queries Mentioning Each Brand
Each (brand, query) pair either matches or it does not, so the natural shape is a join whose condition is a substring test rather than an equality: q.query_text LIKE CONCAT('%', b.brand_name, '%'). The pattern is built per brand row, so every brand is tested against every query.
After the join, each surviving row is one brand mentioned in one query; grouping by the brand and counting gives the number of searches. A query naming two brands joins to both brand rows and so counts once for each, as the statement asks. A brand that no query mentions has no joined rows and drops out of the inner join on its own.
LOCATE(needle, haystack) > 0 and INSTR(haystack, needle) > 0 are equivalent tests (note the reversed argument order) and appear in the alternatives; the second one counts with a correlated subquery and filters out the zero rows. All of these compare every brand with every query — fine for a dimension table this size; at scale a search index or tokenised table would replace the substring scan.
Reference solution (MySQL)
SELECT b.brand_name, COUNT(*) AS searches
FROM Brand b
JOIN SearchQuery q ON q.query_text LIKE CONCAT('%', b.brand_name, '%')
GROUP BY b.brand_id, b.brand_nameAnother way
SELECT b.brand_name, COUNT(q.query_id) AS searches FROM Brand b JOIN SearchQuery q ON LOCATE(b.brand_name, q.query_text) > 0 GROUP BY b.brand_id, b.brand_nameAnother way
SELECT brand_name, searches FROM (SELECT b.brand_name, (SELECT COUNT(*) FROM SearchQuery q WHERE INSTR(q.query_text, b.brand_name) > 0) AS searches FROM Brand b) t WHERE searches > 0← Amazon: Lightning Deals That Sold Out Before Ending · Amazon: Delivery Routes Loaded Beyond Their Van's Capacity →