Microsoft: Bing Search Sessions That Ended Without a Click

Hard
796.

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.

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

ColumnTypeValues
event_idint
user_idint
event_timedatetime
event_typeenumquery, 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.

Example

The rows must come back in the order shown.

SearchEvent

event_iduser_idevent_timeevent_type
112025-07-01 09:00:00query
212025-07-01 09:01:00click
312025-07-01 09:40:00query
412025-07-01 09:45:00query
512025-07-01 09:50:00click
612025-07-01 10:05:00query
712025-07-01 10:35:00query
822025-07-01 14:00:00query
922025-07-01 14:10:00query
1022025-07-01 14:12:00click
3 more rows

Output

user_idsessionsunsuccessful_sessions
121
220
311
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.

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

ColumnType
event_idint
user_idint
event_timedatetime
event_typeenum(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_iduser_idevent_timeevent_type
112025-07-01 09:00:00query
212025-07-01 09:01:00click
312025-07-01 09:40:00query
412025-07-01 09:45:00query
512025-07-01 09:50:00click
612025-07-01 10:05:00query
712025-07-01 10:35:00query
822025-07-01 14:00:00query
922025-07-01 14:10:00query
1022025-07-01 14:12:00click
1122025-07-01 15:00:00query
1232025-07-01 20:00:00query
1332025-07-01 20:20:00query

Output

user_idsessionsunsuccessful_sessions
121
220
311

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_id

Another 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 →