Google: Gmail Spam Filter Precision and Recall per Model

Medium
685.

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.

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

ColumnTypeValues
message_idint
model_versionvarchar
predicted_spambool
rater_labelenumspam, 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.

Example

The rows must come back in the order shown.

LabeledMessage

message_idmodel_versionpredicted_spamrater_label
1v411spam
2v411ham
3v410spam
4v411spam
5v410ham
6v411NULL
7v420ham
8v420ham
9v420spam
10v431NULL

Output

model_versionlabeledprecision_pctrecall_pct
v41566.6766.67
v423NULL0
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.

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

ColumnType
message_idint
model_versionvarchar
predicted_spambool
rater_labelenum(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_idmodel_versionpredicted_spamrater_label
1v411spam
2v411ham
3v410spam
4v411spam
5v410ham
6v411NULL
7v420ham
8v420ham
9v420spam
10v431NULL

Output

model_versionlabeledprecision_pctrecall_pct
v41566.6766.67
v423NULL0

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_version

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