Google: Read Match Type From Ad Keyword Syntax — SQL Problem
In a Google Ads bulk upload, advertisers mark a keyword's match type with punctuation: [running shoes] is exact, "running shoes" is phrase, and plain…
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #679
Problem statement
In a Google Ads bulk upload, advertisers mark a keyword's match type with punctuation: [running shoes] is exact, "running shoes" is phrase, and plain running shoes is broad. An ads-quality engineer needs the type as its own column.
Return keyword_id, keyword (the text with the enclosing brackets or quotes removed) and match_type (exact, phrase or broad). Only text that both starts and ends with the pair counts; anything else, including a half-closed [running shoes, is broad and kept exactly as written. Order by keyword_id.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: AdKeyword
| Column | Type |
|---|---|
| keyword_id | int |
| ad_group_id | int |
| keyword_text | varchar |
Primary key: keyword_id.
Keyword text exactly as uploaded; it has no leading or trailing spaces and is at least three characters long.
Examples
Example 1
AdKeyword
| keyword_id | ad_group_id | keyword_text |
|---|---|---|
| 1 | 10 | [running shoes] |
| 2 | 10 | "running shoes" |
| 3 | 10 | running shoes |
| 4 | 11 | [cricket bat |
| 5 | 11 | "cricket bat" |
| 6 | 12 | cheap flights] |
| 7 | 12 | [jee coaching] |
Output
| keyword_id | keyword | match_type |
|---|---|---|
| 1 | running shoes | exact |
| 2 | running shoes | phrase |
| 3 | running shoes | broad |
| 4 | [cricket bat | broad |
| 5 | cricket bat | phrase |
| 6 | cheap flights] | broad |
| 7 | jee coaching | exact |
How to solve Google: Read Match Type From Ad Keyword Syntax
The match type is decided by the first and last characters together, so each test has to check both ends. keyword_text LIKE '[%]' does exactly that: a literal [, anything, then a literal ] at the very end. (In MySQL's LIKE only % and _ are special, so brackets match themselves.) The phrase test is the same pattern with double quotes. Anything that fails both — plain text, a lone opening bracket, mismatched [ … " — is broad.
For the cleaned keyword, a matched pair is removed with SUBSTRING(keyword_text, 2, CHAR_LENGTH(keyword_text) - 2): start after the first character and take everything but the last. Broad keywords are returned untouched, which is why the half-closed [cricket bat keeps its bracket.
The alternative glues LEFT(…, 1) and RIGHT(…, 1) into a two-character string and switches on it with a simple CASE, computing the type once in a derived table and reusing it. Both read each row once.
Reference solution (MySQL)
SELECT keyword_id,
CASE WHEN keyword_text LIKE '[%]' OR keyword_text LIKE '"%"'
THEN SUBSTRING(keyword_text, 2, CHAR_LENGTH(keyword_text) - 2)
ELSE keyword_text END AS keyword,
CASE WHEN keyword_text LIKE '[%]' THEN 'exact'
WHEN keyword_text LIKE '"%"' THEN 'phrase'
ELSE 'broad' END AS match_type
FROM AdKeyword
ORDER BY keyword_idAnother way
SELECT keyword_id, IF(t = 'broad', keyword_text, SUBSTRING(keyword_text, 2, CHAR_LENGTH(keyword_text) - 2)) AS keyword, t AS match_type FROM (SELECT keyword_id, keyword_text, CASE CONCAT(LEFT(keyword_text, 1), RIGHT(keyword_text, 1)) WHEN '[]' THEN 'exact' WHEN '""' THEN 'phrase' ELSE 'broad' END AS t FROM AdKeyword) k ORDER BY keyword_id← Google: Pixel Units Sold and Returned per Model · Google: Chromebooks Not Synced in 90 Days →