Google: Read Match Type From Ad Keyword Syntax

Easy
679.

Google: Read Match Type From Ad Keyword Syntax

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

ColumnType
keyword_idint
ad_group_idint
keyword_textvarchar

Primary key: keyword_id.

Keyword text exactly as uploaded; it has no leading or trailing spaces and is at least three characters long.

Example

The rows must come back in the order shown.

AdKeyword

keyword_idad_group_idkeyword_text
110[running shoes]
210"running shoes"
310running shoes
411[cricket bat
511"cricket bat"
612cheap flights]
712[jee coaching]

Output

keyword_idkeywordmatch_type
1running shoesexact
2running shoesphrase
3running shoesbroad
4[cricket batbroad
5cricket batphrase
6cheap flights]broad
7jee coachingexact
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.

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

ColumnType
keyword_idint
ad_group_idint
keyword_textvarchar

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_idad_group_idkeyword_text
110[running shoes]
210"running shoes"
310running shoes
411[cricket bat
511"cricket bat"
612cheap flights]
712[jee coaching]

Output

keyword_idkeywordmatch_type
1running shoesexact
2running shoesphrase
3running shoesbroad
4[cricket batbroad
5cricket batphrase
6cheap flights]broad
7jee coachingexact

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_id

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