Long Searches Containing 'er'

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Amazon

Problem

An analyst's typeahead report for the letter 'm' keeps coming up short, and she suspects the queries people typed with capitals are slipping past it. From search_queries, pull every query whose term runs longer than 12 characters and contains an 'm' in any casing, with all of its fields and the term exactly as the user typed it.

Long queries with 'er'. A pattern?

Tables

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

Expected output

Worked solution and explanation

What this really is

This problem is two filters on one column, and the second filter is a case problem. The length cut is easy, and almost everyone gets it right. What separates candidates is whether they notice that search_queries stores the same query under more than one capitalization. The report is coming up short because a raw match for 'm' cannot see an 'M'. Fold case inside the comparison and only there. If you skip it, the capitalized spellings vanish. If you fold case in the SELECT list, you return text that no longer matches the table.

Step 1: Measure the whole term with LENGTH

LENGTH(search_term) > 12 counts every character, spaces included, so a two-word query is judged on its full text. This is the long-tail cut, and it does real work: several short terms also contain an 'm' and must fall out here.

Step 2: Fold case inside the predicate

LOWER(search_term) LIKE '%m%' compares a lowercased copy of the term. Any spelling of the query, whether it starts with 'M' or is typed in all capitals, now matches the same pattern. The two conditions are independent, so join them with AND.

Step 3: Return the rows as stored

Select the six columns directly. The LOWER call exists only for the comparison, so search_term comes back exactly as the user typed it, capitals and all. Nothing in the ask is about order, so no sort is needed.

SELECT query_id, user_id, search_term, results_count, clicked_result, query_time
FROM search_queries
WHERE LENGTH(search_term) > 12
    AND LOWER(search_term) LIKE '%m%'

Raw match

search_term LIKE '%m%' keeps only the spellings that happen to be lowercase where the 'm' sits. The capitalized copies of the same query drop out silently, and the query still runs without error.

Folded match

LOWER(search_term) LIKE '%m%' evaluates every spelling as one form. Every copy of the query is counted, and the returned search_term is untouched because the fold never leaves the WHERE clause.

Common follow-up questions

• Some `search_term` values carry stray leading or trailing spaces. How does that change the length threshold? (Tests whether the candidate sees that padding inflates `LENGTH` and would reach for a trim inside the comparison.)
• The report now needs terms that start with 'm' rather than contain it. What changes? (Tests pattern anchoring and whether the case fold carries over unchanged.)
• How would you make this filter cheap if it ran every minute on the full table? (Tests normalizing at write time versus computing `LOWER` on every read.)