Search: Null Result Pivot
Google, Bing, Duck Duck Go
Problem Statement
At Google, search quality engineers look for "Null Queries" to understand what users are looking for that doesn't yet exist in the index. These "content gaps" are high-priority targets for web crawling or product development.
Write a query to find the top 10 search queries that returned 0 results but have the highest search volume.
Rules:
- A null result is defined as any search where the result_count is exactly 0.
- Volume is the total number of times that specific search_query was searched.
- Case sensitivity: Treat search_query as case-insensitive (e.g., "SQL" and "sql" are the same).
- Output columns: search_query, null_result_volume.
- Sort by null_result_volume descending, then search_query ascending.
Tests your understanding of
Aggregation, Filtering, Ranking and String Manipulation
Input Tables
search_logs
| search_id(INTEGER) | user_id(INTEGER) | search_query(VARCHAR) | result_count(INTEGER) | search_timestamp(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 1 | how to fly | 0 | 2026-03-04 10:00:00 |
| 2 | 2 | how to fly | 0 | 2026-03-04 10:05:00 |
| 3 | 3 | SQL tutorial | 500 | 2026-03-04 10:10:00 |
| 4 | 4 | iPhone 25 pro | 0 | 2026-03-04 10:15:00 |
| 5 | 5 | how to fly | 0 | 2026-03-04 10:20:00 |
| 6 | 6 | IPHONE 25 PRO | 0 | 2026-03-04 10:25:00 |
| 7 | 7 | best pizza mars | 0 | 2026-03-04 10:30:00 |
Expected Output
| search_query(VARCHAR) | null_result_volume(INTEGER) |
|---|---|
| how to fly | 3 |
| iphone 25 pro | 2 |
| best pizza mars | 1 |
Tags
MediumAggregationFilteringRankingString Manipulation
15-25 min
45%