Microsoft: Bing Search Sessions That Ended Without a Click
A Bing search-quality team looks for failed search sessions: the user rephrased the query and still gave up without clicking anything.
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #796
Problem statement
A Bing search-quality team looks for failed search sessions: the user rephrased the query and still gave up without clicking anything.
Split each user's events into sessions: events ordered by event_time (then event_id), and a gap of more than 30 minutes since that user's previous event starts a new session (exactly 30 minutes stays in the same one). A session is unsuccessful when it has at least two queries and its last event is a query. For every user, return user_id, sessions and unsuccessful_sessions, ordered by user_id.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: SearchEvent
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_time | datetime |
| event_type | enum(query, click) |
Primary key: event_id.
One row per query typed or result clicked; times are whole minutes, and a user never has two events at the same time.
Examples
Example 1
SearchEvent
| event_id | user_id | event_time | event_type |
|---|---|---|---|
| 1 | 1 | 2025-07-01 09:00:00 | query |
| 2 | 1 | 2025-07-01 09:01:00 | click |
| 3 | 1 | 2025-07-01 09:40:00 | query |
| 4 | 1 | 2025-07-01 09:45:00 | query |
| 5 | 1 | 2025-07-01 09:50:00 | click |
| 6 | 1 | 2025-07-01 10:05:00 | query |
| 7 | 1 | 2025-07-01 10:35:00 | query |
| 8 | 2 | 2025-07-01 14:00:00 | query |
| 9 | 2 | 2025-07-01 14:10:00 | query |
| 10 | 2 | 2025-07-01 14:12:00 | click |
| 11 | 2 | 2025-07-01 15:00:00 | query |
| 12 | 3 | 2025-07-01 20:00:00 | query |
| 13 | 3 | 2025-07-01 20:20:00 | query |
Output
| user_id | sessions | unsuccessful_sessions |
|---|---|---|
| 1 | 2 | 1 |
| 2 | 2 | 0 |
| 3 | 1 | 1 |
How to solve Microsoft: Bing Search Sessions That Ended Without a Click
This is sessionisation followed by a per-session test. LAG(event_time) over each user's events gives the time since their previous event; the event opens a session when there is no previous event or the gap is more than 30 minutes. A running SUM of those 0/1 flags gives every event its session number. User 1's event at 10:35 comes exactly 30 minutes after 10:05, so it stays in the same session — the boundary the statement fixes.
Each session then needs two facts: how many queries it holds, and what its final event was. ROW_NUMBER() ordered by time descending marks the last event as position 1, and a conditional MAX lifts its type into the session row. A session is unsuccessful when it has two or more queries (the user reformulated) and ends on a query (they never clicked after the last attempt). User 2's lone 15:00 query is a session but not a failed one — a single query with no click is a different signal.
The alternative finds starts with NOT EXISTS (no event in the previous 30 minutes), tags each event with its latest start, and checks the last event with EXISTS. Correlated and quadratic per user; the window version is a few sorts.
Reference solution (MySQL)
WITH gaps AS (
SELECT e.*, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS prev_time
FROM SearchEvent e
), numbered AS (
SELECT g.*, SUM(CASE WHEN prev_time IS NULL OR TIMESTAMPDIFF(MINUTE, prev_time, event_time) > 30 THEN 1 ELSE 0 END)
OVER (PARTITION BY user_id ORDER BY event_time, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM gaps g
), from_end AS (
SELECT n.*, ROW_NUMBER() OVER (PARTITION BY user_id, session_no ORDER BY event_time DESC, event_id DESC) AS pos_from_end
FROM numbered n
), per_session AS (
SELECT user_id, session_no,
SUM(CASE WHEN event_type = 'query' THEN 1 ELSE 0 END) AS queries,
MAX(CASE WHEN pos_from_end = 1 THEN event_type END) AS last_type
FROM from_end
GROUP BY user_id, session_no
)
SELECT user_id, COUNT(*) AS sessions,
SUM(CASE WHEN queries >= 2 AND last_type = 'query' THEN 1 ELSE 0 END) AS unsuccessful_sessions
FROM per_session
GROUP BY user_id
ORDER BY user_idAnother way
WITH starts AS (
SELECT a.user_id, a.event_time AS started_at
FROM SearchEvent a
WHERE NOT EXISTS (SELECT 1 FROM SearchEvent b
WHERE b.user_id = a.user_id AND b.event_time < a.event_time
AND b.event_time >= DATE_SUB(a.event_time, INTERVAL 30 MINUTE))
), tagged AS (
SELECT e.user_id, e.event_time, e.event_type,
(SELECT MAX(s.started_at) FROM starts s WHERE s.user_id = e.user_id AND s.started_at <= e.event_time) AS started_at
FROM SearchEvent e
), per_session AS (
SELECT user_id, started_at, SUM(CASE WHEN event_type = 'query' THEN 1 ELSE 0 END) AS queries, MAX(event_time) AS last_time
FROM tagged GROUP BY user_id, started_at
)
SELECT p.user_id, COUNT(*) AS sessions,
SUM(CASE WHEN p.queries >= 2 AND EXISTS (SELECT 1 FROM tagged t WHERE t.user_id = p.user_id AND t.event_time = p.last_time AND t.event_type = 'query')
THEN 1 ELSE 0 END) AS unsuccessful_sessions
FROM per_session p
GROUP BY p.user_id
ORDER BY p.user_id← Microsoft: Xbox Hunters With the Most Rare Achievements · Microsoft: Game Pass Win-Back Rate by Cancellation Month →