Google: Searches Reformulated Within a Minute

Medium
687.

Google: Searches Reformulated Within a Minute

A Search quality rater flags queries that users quickly rewrite: if someone searches, clicks nothing and searches again within a minute, the first results page probably missed.

A search is reformulated when clicked = 0 and the same user's next search comes at most 60 seconds later (inclusive). For each query_text with at least one reformulated search, return query_text, searches (all its searches), reformulated and reformulation_pct = 100 × reformulated ÷ searches, rounded to 2 decimals. Order by reformulation_pct descending, then query_text 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: SearchEvent

ColumnType
search_idint
user_idint
query_textvarchar
searched_atdatetime
clickedbool

Primary key: search_id.

One search. A user never searches twice in the same second. clicked is 1 if any result of that search was clicked.

Example

The rows must come back in the order shown.

SearchEvent

search_iduser_idquery_textsearched_atclicked
11biryani near me2025-05-01 12:00:000
21biryani delivery koramangala2025-05-01 12:00:401
31weather pune2025-05-01 18:00:000
42biryani near me2025-05-01 13:00:000
52biryani near me open now2025-05-01 13:01:000
62mutton biryani recipe2025-05-01 13:02:010
73weather pune2025-05-02 07:00:001
83weather mumbai2025-05-02 07:00:200
94weather pune2025-05-02 08:00:000
104weather pune today2025-05-02 08:00:301

Output

query_textsearchesreformulatedreformulation_pct
biryani near me22100
weather pune3133.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Google: Searches Reformulated Within a Minute — SQL Problem

A Search quality rater flags queries that users quickly rewrite: if someone searches, clicks nothing and searches again within a minute, the first results…

  • Difficulty: Medium
  • Topics: Window Functions, Dates
  • Dialect: MySQL
  • Problem: #687

Problem statement

A Search quality rater flags queries that users quickly rewrite: if someone searches, clicks nothing and searches again within a minute, the first results page probably missed.

A search is reformulated when clicked = 0 and the same user's next search comes at most 60 seconds later (inclusive). For each query_text with at least one reformulated search, return query_text, searches (all its searches), reformulated and reformulation_pct = 100 × reformulated ÷ searches, rounded to 2 decimals. Order by reformulation_pct descending, then query_text 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: SearchEvent

ColumnType
search_idint
user_idint
query_textvarchar
searched_atdatetime
clickedbool

Primary key: search_id.

One search. A user never searches twice in the same second. clicked is 1 if any result of that search was clicked.

Examples

Example 1

SearchEvent

search_iduser_idquery_textsearched_atclicked
11biryani near me2025-05-01 12:00:000
21biryani delivery koramangala2025-05-01 12:00:401
31weather pune2025-05-01 18:00:000
42biryani near me2025-05-01 13:00:000
52biryani near me open now2025-05-01 13:01:000
62mutton biryani recipe2025-05-01 13:02:010
73weather pune2025-05-02 07:00:001
83weather mumbai2025-05-02 07:00:200
94weather pune2025-05-02 08:00:000
104weather pune today2025-05-02 08:00:301

Output

query_textsearchesreformulatedreformulation_pct
biryani near me22100
weather pune3133.33

How to solve Google: Searches Reformulated Within a Minute

Whether a search was reformulated depends on the next search of the same user, so LEAD(searched_at) OVER (PARTITION BY user_id ORDER BY searched_at) puts that time on every row. A search is flagged when nothing was clicked and the gap, measured exactly with TIMESTAMPDIFF(SECOND, …), is at most 60. The last search of a user has a NULL next time; the comparison is then unknown and the CASE falls to 0, so it is never a reformulation.

Once each search carries a 1/0 flag, the report is a grouped aggregate per query_text: count all searches, sum the flags, and compute the percentage. HAVING keeps only queries that were reformulated at least once. The order is fully determined by the percentage and then the text.

The alternative asks the same question with a correlated EXISTS: is there any later search by the user within 60 seconds? Because the earliest later search is the next one, "some later search within a minute" and "the next search within a minute" are the same condition. The window version sorts each user's searches once; the EXISTS one probes per row.

Reference solution (MySQL)

WITH s AS (
  SELECT query_text, clicked, searched_at,
         LEAD(searched_at) OVER (PARTITION BY user_id ORDER BY searched_at) AS next_at
  FROM SearchEvent
), flagged AS (
  SELECT query_text,
         CASE WHEN clicked = 0 AND TIMESTAMPDIFF(SECOND, searched_at, next_at) <= 60 THEN 1 ELSE 0 END AS refo
  FROM s
)
SELECT query_text,
       COUNT(*) AS searches,
       SUM(refo) AS reformulated,
       ROUND(100 * SUM(refo) / COUNT(*), 2) AS reformulation_pct
FROM flagged
GROUP BY query_text
HAVING SUM(refo) >= 1
ORDER BY reformulation_pct DESC, query_text

Another way

SELECT query_text, COUNT(*) AS searches, SUM(r) AS reformulated, ROUND(100 * SUM(r) / COUNT(*), 2) AS reformulation_pct FROM (SELECT e.query_text, IF(e.clicked = 0 AND EXISTS (SELECT 1 FROM SearchEvent n WHERE n.user_id = e.user_id AND n.searched_at > e.searched_at AND n.searched_at <= DATE_ADD(e.searched_at, INTERVAL 60 SECOND)), 1, 0) AS r FROM SearchEvent e) x GROUP BY query_text HAVING SUM(r) > 0 ORDER BY reformulation_pct DESC, query_text

← Google: Play Installs Removed Within a Week · Google: Longest Heartbeat Gap of Each Nest Device →