Google: Zero-Result Searches per Locale — SQL Problem
A Search quality analyst wants to know which language markets most often hit an empty results page, because those queries point at content gaps in the index.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #673
Problem statement
A Search quality analyst wants to know which language markets most often hit an empty results page, because those queries point at content gaps in the index.
For every locale that appears in SearchLog, return locale, total_searches (all its searches) and zero_result_searches (its searches with result_count = 0). Order the rows by zero_result_searches descending, then locale ascending.
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: SearchLog
| Column | Type |
|---|---|
| search_id | int |
| locale | varchar |
| query_text | varchar |
| result_count | int |
| searched_at | datetime |
Primary key: search_id.
One row per search. result_count is the number of organic results returned and is never NULL.
Examples
Example 1
SearchLog
| search_id | locale | query_text | result_count | searched_at |
|---|---|---|---|---|
| 1 | hi-IN | monsoon session dates | 0 | 2025-05-01 09:12:00 |
| 2 | en-IN | weather pune | 412 | 2025-05-01 09:15:30 |
| 3 | hi-IN | rangoli designs | 98 | 2025-05-01 10:02:11 |
| 4 | ta-IN | metro card recharge | 0 | 2025-05-01 11:40:00 |
| 5 | en-IN | gate exam syllabus | 0 | 2025-05-01 12:00:05 |
| 6 | ta-IN | biryani near me | 0 | 2025-05-01 13:22:47 |
| 7 | bn-IN | diwali sale | 250 | 2025-05-01 14:05:09 |
| 8 | hi-IN | upi limit per day | 0 | 2025-05-01 15:30:00 |
Output
| locale | total_searches | zero_result_searches |
|---|---|---|
| hi-IN | 3 | 2 |
| ta-IN | 2 | 2 |
| en-IN | 2 | 1 |
| bn-IN | 1 | 0 |
How to solve Google: Zero-Result Searches per Locale
Each output row summarises one locale, so the query groups SearchLog by locale. Inside a group two counts are needed at once. COUNT(*) is the total, and the zero-result count is conditional aggregation: SUM(CASE WHEN result_count = 0 THEN 1 ELSE 0 END) adds one for every empty results page and nothing for the rest. Because the CASE returns 0 rather than NULL for other rows, a locale whose searches all found something still shows 0, not NULL.
Only locales that appear in the log can be grouped, which is exactly what the statement asks. The order is fully fixed: zero-result count descending, then the locale name to break ties, so two markets with the same count always come out the same way.
COUNT(IF(result_count = 0, 1, NULL)) is the same idea — COUNT skips the NULLs. A correlated subquery per locale also works but reads the table again for each group; the single grouped scan is linear.
Reference solution (MySQL)
SELECT locale,
COUNT(*) AS total_searches,
SUM(CASE WHEN result_count = 0 THEN 1 ELSE 0 END) AS zero_result_searches
FROM SearchLog
GROUP BY locale
ORDER BY zero_result_searches DESC, localeAnother way
SELECT locale, COUNT(*) AS total_searches, COUNT(IF(result_count = 0, 1, NULL)) AS zero_result_searches FROM SearchLog GROUP BY locale ORDER BY zero_result_searches DESC, localeAnother way
SELECT l.locale, l.total_searches, (SELECT COUNT(*) FROM SearchLog z WHERE z.locale = l.locale AND z.result_count = 0) AS zero_result_searches FROM (SELECT locale, COUNT(*) AS total_searches FROM SearchLog GROUP BY locale) l ORDER BY zero_result_searches DESC, l.locale← Flipkart: How Often Each Product's Buy Box Changed Hands · Google: Published Play Apps With No Installs →