Google: Gmail Spam Filter Precision and Recall per Model
Before promoting a new Gmail spam model, the anti-abuse team compares model versions on a sample of messages that human raters labelled.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #685
Problem statement
Before promoting a new Gmail spam model, the anti-abuse team compares model versions on a sample of messages that human raters labelled.
Using only messages with a rater_label, return for each model_version that has at least one such message: model_version, labeled (labelled messages), precision_pct = 100 × (predicted spam and labelled spam) ÷ (predicted spam) and recall_pct = 100 × (predicted spam and labelled spam) ÷ (labelled spam), each rounded to 2 decimals, and NULL when its denominator is 0. Order by model_version.
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: LabeledMessage
| Column | Type |
|---|---|
| message_id | int |
| model_version | varchar |
| predicted_spam | bool |
| rater_label | enum(spam, ham) |
Primary key: message_id.
One sampled message scored by one model version. predicted_spam is the model's call; rater_label is the human verdict, or NULL if not yet rated.
Examples
Example 1
LabeledMessage
| message_id | model_version | predicted_spam | rater_label |
|---|---|---|---|
| 1 | v41 | 1 | spam |
| 2 | v41 | 1 | ham |
| 3 | v41 | 0 | spam |
| 4 | v41 | 1 | spam |
| 5 | v41 | 0 | ham |
| 6 | v41 | 1 | NULL |
| 7 | v42 | 0 | ham |
| 8 | v42 | 0 | ham |
| 9 | v42 | 0 | spam |
| 10 | v43 | 1 | NULL |
Output
| model_version | labeled | precision_pct | recall_pct |
|---|---|---|---|
| v41 | 5 | 66.67 | 66.67 |
| v42 | 3 | NULL | 0 |
How to solve Google: Gmail Spam Filter Precision and Recall per Model
Precision and recall are both ratios of counts from the same group of messages, so the query is one GROUP BY model_version over the rated messages, with conditional sums for the pieces: true positives (predicted spam and labelled spam), all predicted spam (true + false positives) and all labelled spam (true positives + false negatives). Unrated messages are removed in WHERE first — they would otherwise inflate the predicted-spam count with calls nobody checked.
A version can have no predicted spam (a cautious model) or no labelled spam in its sample, and then a ratio is undefined. NULLIF(denominator, 0) makes the division return NULL instead of failing, and ROUND of NULL stays NULL. A version whose messages are all unrated has no rows after the filter and does not appear.
The alternative computes TP, FP and FN in a CTE and spells the zero cases with CASE, which reads closer to the textbook definitions. Both are one scan.
Reference solution (MySQL)
SELECT model_version,
COUNT(*) AS labeled,
ROUND(100 * SUM(CASE WHEN predicted_spam = 1 AND rater_label = 'spam' THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN predicted_spam = 1 THEN 1 ELSE 0 END), 0), 2) AS precision_pct,
ROUND(100 * SUM(CASE WHEN predicted_spam = 1 AND rater_label = 'spam' THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN rater_label = 'spam' THEN 1 ELSE 0 END), 0), 2) AS recall_pct
FROM LabeledMessage
WHERE rater_label IS NOT NULL
GROUP BY model_version
ORDER BY model_versionAnother way
WITH c AS (SELECT model_version, COUNT(*) AS labeled, SUM(IF(predicted_spam = 1 AND rater_label = 'spam', 1, 0)) AS tp, SUM(IF(predicted_spam = 1 AND rater_label = 'ham', 1, 0)) AS fp, SUM(IF(predicted_spam = 0 AND rater_label = 'spam', 1, 0)) AS fn FROM LabeledMessage WHERE rater_label IS NOT NULL GROUP BY model_version) SELECT model_version, labeled, CASE WHEN tp + fp = 0 THEN NULL ELSE ROUND(100 * tp / (tp + fp), 2) END AS precision_pct, CASE WHEN tp + fn = 0 THEN NULL ELSE ROUND(100 * tp / (tp + fn), 2) END AS recall_pct FROM c ORDER BY model_version← Google: Places per Top-Level Maps Category · Google: Play Installs Removed Within a Week →