Search Terms Starting With G

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

Google

Problem

The search relevance team is debugging why queries starting with 'g' return fewer results than expected. Pull all such search terms alongside their results count.

Queries starting with 'g'.

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

This is a prefix filter that has to leave the evidence alone. Matching terms that begin with 'g' is the easy half. LIKE 'g%' does it, while = 'g' matches nothing and '%g%' drags in 'ergonomic chair' and 'fast charger'. The half that separates candidates is restraint. The team is chasing 'g' searches that come back short, and the rows with no results_count are exactly those searches. Add a reflexive IS NOT NULL, a results_count > 0, a DISTINCT or a GROUP BY, and the report shows healthy 'gaming mouse' rows and no problem at all.

Getting to the query

Step 1: Anchor the pattern at the start of search_term

'Starting with g' is a prefix, so the wildcard goes after the letter: LIKE 'g%'. Equality only matches the one-letter string 'g', which nobody searched. A wildcard on both sides finds a 'g' anywhere and over-includes 'rgb lighting', 'cable management' and half the catalog.

Step 2: Keep every matching search as its own row

Ten searches match: eight 'gaming mouse' and two 'gming mouse'. The repeats are separate searches, and both 'gming mouse' searches logged no results_count. No DISTINCT, no GROUP BY and no null filter: each one erases part of what the team is debugging.

Step 3: Return the term and its count, nothing more

Project search_term and results_count and stop. query_id, user_id and query_time would help a later drill-down, but they are not what was asked for.

SELECT search_term, results_count
FROM search_queries
WHERE search_term LIKE 'g%'

Cleaned up

WHERE search_term LIKE 'g%' AND results_count IS NOT NULL returns eight 'gaming mouse' rows with counts such as 68, 408 and 248. Every 'g' search looks fine.

As logged

WHERE search_term LIKE 'g%' alone returns ten rows. The two extra rows are 'gming mouse' with an empty count, and that typo is where the results go missing.

Common follow-up questions

• How would you show each 'g' term once, with its number of searches and how many of them logged no `results_count`? (Tests moving from row-level evidence to a summary without losing the NULL signal.)
• Some terms are stored with stray spaces, like ' 4k monitor', and one as 'Mechanical Keyboard'. How would you make a prefix match robust to both? (Tests `TRIM` and `LOWER` before the pattern, and whether the index still helps afterwards.)
• The team now wants only the last 30 days. What changes in the plan on a table partitioned by `query_time`? (Tests partition pruning: a date bound turns a 365-partition scan into about 30.)