Amazon: Search Queries Mentioning Each Brand

Easy
574.

Amazon: Search Queries Mentioning Each Brand

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

ColumnType
brand_idint
brand_namevarchar

Primary key: brand_id.

One row per brand sold on the store; names are lower case and contain only letters.

Table: SearchQuery

ColumnType
query_idint
query_textvarchar
searched_ondate

Primary key: query_id.

One row per search typed by a shopper, lower-cased.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Brand

brand_idbrand_name
1echo
2boat
3kindle
4prestige
5puma

SearchQuery

query_idquery_textsearched_on
101echo dot 5th gen2025-03-01
102boat airdopes 1412025-03-01
103kindle paperwhite cover2025-03-02
104echo show vs kindle for kids2025-03-02
105usb c fast charger2025-03-03
106boat smartwatch2025-03-03
107echo2025-03-04

Output

brand_namesearches
echo3
boat2
kindle2
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.

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

ColumnType
brand_idint
brand_namevarchar

Primary key: brand_id.

One row per brand sold on the store; names are lower case and contain only letters.

Table: SearchQuery

ColumnType
query_idint
query_textvarchar
searched_ondate

Primary key: query_id.

One row per search typed by a shopper, lower-cased.

Examples

Example 1

Brand

brand_idbrand_name
1echo
2boat
3kindle
4prestige
5puma

SearchQuery

query_idquery_textsearched_on
101echo dot 5th gen2025-03-01
102boat airdopes 1412025-03-01
103kindle paperwhite cover2025-03-02
104echo show vs kindle for kids2025-03-02
105usb c fast charger2025-03-03
106boat smartwatch2025-03-03
107echo2025-03-04

Output

brand_namesearches
echo3
boat2
kindle2

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_name

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

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