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
| Column | Type |
|---|---|
| query_id | int |
| market | varchar |
| is_bot | bool |
| issued_at | datetime |
Primary key: query_id.
One row per search; market is a locale code such as en-IN.
Table: ResultClick
| Column | Type |
|---|---|
| click_id | int |
| query_id | int |
| position | int |
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_id | market | is_bot | issued_at |
|---|---|---|---|
| 1 | en-IN | 0 | 2025-08-01 09:00:00 |
| 2 | en-IN | 0 | 2025-08-01 09:02:00 |
| 3 | en-IN | 0 | 2025-08-01 09:05:00 |
| 4 | en-IN | 0 | 2025-08-01 09:07:00 |
| 5 | en-US | 0 | 2025-08-01 10:00:00 |
| 6 | en-US | 0 | 2025-08-01 10:01:00 |
| 7 | en-US | 0 | 2025-08-01 10:03:00 |
| 8 | en-US | 1 | 2025-08-01 10:04:00 |
| 9 | fr-FR | 1 | 2025-08-01 11:00:00 |
| 10 | de-DE | 0 | 2025-08-01 12:00:00 |
| 11 | ja-JP | 0 | 2025-08-01 13:00:00 |
ResultClick
| click_id | query_id | position |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 3 | 2 |
| 4 | 5 | 1 |
| 5 | 6 | 4 |
| 6 | 11 | 1 |
Output
| market | queries | abandoned_queries | abandonment_pct |
|---|---|---|---|
| de-DE | 1 | 1 | 100 |
| en-IN | 4 | 2 | 50 |
| en-US | 3 | 1 | 33.33 |
| ja-JP | 1 | 0 | 0 |
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, marketAnother 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 →