Average Results for Python Searches

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

City and County of San Francisco

Problem

Over the last 90 days, shoppers searching for keyboards have complained about empty result pages, and the search team wants that measured from the search_queries log. Find the average number of results returned for those searches, counting any term that contains 'keyboard' however it is capitalized.

Python searches. What's the click-through?

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 is a filtered average with two ways to quietly lose rows. Anyone can write AVG(results_count). What separates candidates is whether the match survives dirty text and whether the average covers the period the complaint is about. On this log one of the recent searches is stored as 'Mechanical Keyboard'. A case-sensitive test drops it and reports 345. Forget the 90-day window and years of old searches pull the answer down to 265. The right number is 371.667.

Step 1: Window the log to the last 90 days

The complaint is about recent result pages, so cut search_queries to query_time >= '2026-06-14' first. That keeps the average about the traffic people are complaining about, and on a table partitioned by query_time it is also the filter that keeps the scan small.

Step 2: Flag keyboard searches with the case folded

Inside the classified CTE, INSTR(LOWER(search_term), 'keyboard') > 0 marks every term that mentions keyboards, whatever the capitalization and wherever the word sits. The flag is computed once and named is_keyboard_search, so the final SELECT reads as the answer.

Step 3: Average only the flagged rows

AVG(CASE WHEN is_keyboard_search = 1 THEN results_count END) hands AVG a NULL for every other search, and AVG skips NULLs. The denominator is therefore the number of recent keyboard searches, not every search in the window.

WITH classified AS (
    SELECT
        results_count,
        CASE
            WHEN INSTR(LOWER(search_term), 'keyboard') > 0 THEN 1
            ELSE 0
        END AS is_keyboard_search
    FROM search_queries
    WHERE query_time >= '2026-06-14'
)
SELECT
    AVG(CASE WHEN is_keyboard_search = 1 THEN results_count END) AS avg_results
FROM classified;

Whole log

No filter on query_time: all ten keyboard searches from 2022 onward are averaged, and the result is 265. That figure describes history, not the result pages people are complaining about now.

Last 90 days

query_time >= '2026-06-14' keeps the three recent keyboard searches, and the result is 371.667. The window changes the answer, so it is part of the question, not decoration.

Common follow-up questions

• Some terms in `search_queries` carry leading or trailing spaces. Does that affect this match, and when would it start to matter? (Tests whether the candidate sees that a substring test tolerates padding while an equality test does not.)
• Product now wants the same average for every product family (keyboards, monitors, headphones) side by side. How does the query change? (Tests moving from one conditional average to one per family, or to a lookup table of terms.)
• How would you also report what share of recent keyboard searches returned zero results? (Tests a second conditional aggregate over the same flag in the same scan.)