Start by clarifying the schema and business definitions (e.g., what constitutes a send, click, internal account, and the date filter). Then structure the query using CTEs: first deduplicate sends to the earliest per user per campaign after the given date, filter out internal users, join clicks within 48 hours, and aggregate per variant. Finally, compute CTR and use window functions or a self-join to calculate the absolute lift between test and control, optionally including counts for confidence interval calculation.
Pro tip: Mention that you would validate the 48-hour window logic by checking edge cases (e.g., clicks exactly at 48 hours) and ensure that the deduplication uses the earliest send per user per campaign, not per variant, to avoid skewing the experiment. Also, note that for confidence intervals, you'd likely use a two-proportion z-test and provide the necessary counts (e.g., successes and trials per variant).
Ask about table structures, definitions of send, click, internal accounts, and the date filter. Confirm that CTR is based on distinct users and that the 48-hour window is from send time.
Use a CTE with ROW_NUMBER() partitioned by user and campaign, ordered by send time, to select the earliest send per user per campaign on or after the given date. Exclude internal accounts.
Join the deduplicated sends to clicks on user and campaign, where click time is between send time and send time + 48 hours. Use DISTINCT to count unique users who clicked.
Group by campaign and variant to get send counts and unique clickers. Compute CTR as unique clickers divided by send counts.
Use a self-join or window functions to compute the absolute lift between test and control. Include counts (e.g., successes and trials) for each variant to enable downstream confidence interval calculation.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The PR curve part was fine, sklearn makes it easy.
Start by clarifying the data schema and metric definitions, then outline a vectorized pandas/numpy solution that leverages sklearn's precision_recall_curve and custom threshold search. Emphasize deterministic tie-breaking and weighted F1 computation, and discuss trade-offs between exact and approximate methods for large datasets.
Pro tip: Mention that you would validate the threshold on a holdout set and consider the business impact of precision at top 1%, as Uber often cares about high-precision interventions. Also, use numpy's argsort with stable kind for deterministic tie-breaking.
Confirm column names for true labels, predicted probabilities, and sample weights. Ask about the expected size of the DataFrame and whether ties in predicted scores are common.
Use sklearn.metrics.precision_recall_curve with sample weights to compute precision and recall at various thresholds, then plot using matplotlib. Note that precision_recall_curve does not directly support sample weights, so you may need to compute weighted precision and recall manually.
Compute weighted F1 for each threshold by combining weighted precision and recall. Use numpy to vectorize the calculation and find the threshold with the maximum F1, handling ties by choosing the smallest threshold or as specified.
Sort the DataFrame by predicted score descending, using a stable sort to break ties deterministically (e.g., by original index). Select the top 1% of samples, compute weighted precision (sum of weights for true positives divided by sum of weights for selected samples).
Mention that for large datasets, approximate methods or sampling may be needed. Suggest validating the chosen threshold on a holdout set and considering business metrics beyond F1.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
ROC-AUC uses the full negative set in its denominator so at 1% prevalence you have a massive negative class that makes it easy to look good even if your positive precision is garbage.
Start by explaining the mathematical relationship between ROC-AUC and PR-AUC under class imbalance, emphasizing how the large number of true negatives inflates ROC-AUC while PR-AUC focuses on the positive class. Then, for the marketing CTR use case, argue that PR-AUC is more informative because it directly measures performance on the rare positive class (clicks), and discuss how business costs and benefits might further guide metric selection.
Pro tip: Mention that in highly imbalanced settings, ROC-AUC can be misleadingly high even for a poor model, and that PR-AUC's baseline equals the prevalence (1%), so 0.18 is actually 18x better than random. This shows you understand the practical implications and can communicate them to stakeholders.
Explain that ROC-AUC evaluates the trade-off between true positive rate and false positive rate across all thresholds, while PR-AUC evaluates the trade-off between precision and recall for the positive class.
With 1% prevalence, the number of negatives is 99 times the positives. ROC-AUC remains high because the false positive rate is diluted by the large number of true negatives, whereas PR-AUC is sensitive to false positives and drops sharply.
For instance, if the model identifies 100 true positives and 500 false positives, the false positive rate is only 500/9900 ≈ 5%, but precision is 100/600 ≈ 17%, leading to low PR-AUC.
In CTR prediction, the positive class (clicks) is rare and the business cares about precision (avoiding wasted impressions) and recall (capturing clicks). PR-AUC directly reflects these trade-offs, while ROC-AUC can be overly optimistic.
Advocate for optimizing PR-AUC (or a related metric like precision@k or lift) because it aligns with business goals, but also consider the specific costs of false positives and false negatives to choose the final metric.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.