Microsoft: Bing Query Abandonment Rate by Market

Medium
786.

Microsoft: Bing Query Abandonment Rate by Market

A Bing relevance analyst uses the abandonment rate — the share of searches where the user clicked no result — as a first signal that a market's results are poor.

Ignoring bot traffic (is_bot = 1), for each market with at least one human query return market, queries (human queries), abandoned_queries (human queries with no click at all) and abandonment_pct = 100 × abandoned ÷ queries, rounded to 2 decimals. A query clicked several times still counts once. Order by abandonment_pct descending, then market ascending.

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: BingQuery

ColumnType
query_idint
marketvarchar
is_botbool
issued_atdatetime

Primary key: query_id.

One row per search; market is a locale code such as en-IN.

Table: ResultClick

ColumnType
click_idint
query_idint
positionint

Primary key: click_id.

One row per click on a result of a query; position is the result's rank on the page.

Example

The rows must come back in the order shown.

BingQuery

query_idmarketis_botissued_at
1en-IN02025-08-01 09:00:00
2en-IN02025-08-01 09:02:00
3en-IN02025-08-01 09:05:00
4en-IN02025-08-01 09:07:00
5en-US02025-08-01 10:00:00
6en-US02025-08-01 10:01:00
7en-US02025-08-01 10:03:00
8en-US12025-08-01 10:04:00
9fr-FR12025-08-01 11:00:00
10de-DE02025-08-01 12:00:00
1 more row

ResultClick

click_idquery_idposition
111
213
332
451
564
6111

Output

marketqueriesabandoned_queriesabandonment_pct
de-DE11100
en-IN4250
en-US3133.33
ja-JP100
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 Query Abandonment Rate by Market

A Bing relevance analyst uses the abandonment rate — the share of searches where the user clicked no result — as a first signal that a market's results are…

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #786

Problem statement

A Bing relevance analyst uses the abandonment rate — the share of searches where the user clicked no result — as a first signal that a market's results are poor.

Ignoring bot traffic (is_bot = 1), for each market with at least one human query return market, queries (human queries), abandoned_queries (human queries with no click at all) and abandonment_pct = 100 × abandoned ÷ queries, rounded to 2 decimals. A query clicked several times still counts once. Order by abandonment_pct descending, then market ascending.

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: BingQuery

ColumnType
query_idint
marketvarchar
is_botbool
issued_atdatetime

Primary key: query_id.

One row per search; market is a locale code such as en-IN.

Table: ResultClick

ColumnType
click_idint
query_idint
positionint

Primary key: click_id.

One row per click on a result of a query; position is the result's rank on the page.

Examples

Example 1

BingQuery

query_idmarketis_botissued_at
1en-IN02025-08-01 09:00:00
2en-IN02025-08-01 09:02:00
3en-IN02025-08-01 09:05:00
4en-IN02025-08-01 09:07:00
5en-US02025-08-01 10:00:00
6en-US02025-08-01 10:01:00
7en-US02025-08-01 10:03:00
8en-US12025-08-01 10:04:00
9fr-FR12025-08-01 11:00:00
10de-DE02025-08-01 12:00:00
11ja-JP02025-08-01 13:00:00

ResultClick

click_idquery_idposition
111
213
332
451
564
6111

Output

marketqueriesabandoned_queriesabandonment_pct
de-DE11100
en-IN4250
en-US3133.33
ja-JP100

How to solve Microsoft: Bing Query Abandonment Rate by Market

The unit is a query, and the only fact needed per query is whether any click exists. EXISTS answers that without multiplying rows: a query with two clicks is still one clicked query. Joining ResultClick directly and counting would inflate the totals for every multi-click query — the most common bug in this kind of metric.

Bot traffic is removed in WHERE before anything is counted, so it affects neither the denominator nor the abandoned count, and a market with only bot traffic forms no group. Then per market: COUNT(*) human queries, SUM(abandoned), and the percentage — computed with 100 * … / …, which is decimal division in MySQL, then rounded. Ties on the rounded rate are broken by market.

The alternative LEFT JOINs to SELECT DISTINCT query_id FROM ResultClick, which de-duplicates clicks first, and counts abandoned queries as COUNT(*) - COUNT(c.query_id). Both are linear in queries plus clicks.

Reference solution (MySQL)

WITH human AS (
  SELECT q.market,
         CASE WHEN EXISTS (SELECT 1 FROM ResultClick c WHERE c.query_id = q.query_id) THEN 0 ELSE 1 END AS abandoned
  FROM BingQuery q
  WHERE q.is_bot = 0
)
SELECT market, COUNT(*) AS queries, SUM(abandoned) AS abandoned_queries,
       ROUND(100 * SUM(abandoned) / COUNT(*), 2) AS abandonment_pct
FROM human
GROUP BY market
ORDER BY abandonment_pct DESC, market

Another way

SELECT q.market, COUNT(*) AS queries, COUNT(*) - COUNT(c.query_id) AS abandoned_queries,
       ROUND(100 * (COUNT(*) - COUNT(c.query_id)) / COUNT(*), 2) AS abandonment_pct
FROM BingQuery q
LEFT JOIN (SELECT DISTINCT query_id FROM ResultClick) c ON c.query_id = q.query_id
WHERE q.is_bot = 0
GROUP BY q.market
ORDER BY abandonment_pct DESC, q.market

← Microsoft: Days Between Windows Insider Flights per Channel · Microsoft: Top Human Contributor per GitHub Repository in 2025 →