Model Evaluation and Validation Questions

Total = TP+FP+FN+TN = 90+10+30+870 = 1000.

Metrics:

• Accuracy = (TP + TN) / Total = (90 + 870) / 1000 = 960 / 1000 = 0.96 (96%).
• Precision = TP / (TP + FP) = 90 / (90 + 10) = 90 / 100 = 0.90 (90%).
• Recall (Sensitivity) = TP / (TP + FN) = 90 / (90 + 30) = 90 / 120 = 0.75 (75%).
• F1 = 2 * (Precision * Recall) / (Precision + Recall) = 2 * (0.90 * 0.75) / (0.90 + 0.75) = 1.35 / 1.65 ≈ 0.818 (81.8%).
• False Positive Rate (FPR) = FP / (FP + TN) = 10 / (10 + 870) = 10 / 880 ≈ 0.01136 (≈1.14%).

What each metric tells you (esp. with rare positives):

• Accuracy (96%): overall fraction correct. It’s high here largely because negatives dominate.
• Precision (90%): of predicted positives, 90% are true: model makes few false alarms.
• Recall (75%): model detects 75% of actual positives but misses 25% (FN); important when missing positives is costly.
• F1 (81.8%): the harmonic mean of precision and recall, a single balanced number that drops sharply if either one is low; useful when you want one figure that doesn't let a strong precision hide a weak recall or vice versa.
• FPR (~1.14%): small fraction of negatives incorrectly flagged positive: useful when false alarms have a cost.

Why accuracy can be misleading:

• With class imbalance (positives are rare here: 120/1000 = 12%), a model that predicts mostly negatives can achieve high accuracy while performing poorly on the minority class. Example: always predicting “negative” gets every actual negative right and every actual positive wrong. Actual negatives = FP + TN = 10 + 870 = 880 (not TN alone), so accuracy = 880/1000 = 88% (not 870/1000 = 87%, the TN count by itself) but 0% recall, since none of the 120 actual positives were ever caught. Thus accuracy hides minority-class performance; prefer precision/recall, F1, PR curve, or cost-weighted metrics and threshold tuning. In production, choose metrics aligned with business costs of false positives vs false negatives and monitor per-class performance.

Approach: Treat this as an A/B test of pipelines (baseline vs. EDA-derived transformations) run reproducibly across multiple datasets and model families. Use a locked holdout to avoid peeking and nested cross-validation for honest tuning.

Experiment design:

• Datasets: choose several representative datasets (size, missingness, skew) relevant to business problems.
• Pipelines: define Baseline pipeline (standard minimal cleaning: simple mean/mode imputation, no log/binning) and Transformed pipeline (apply EDA-suggested transforms: log for skewed features, specified binning, targeted imputation strategy).
• Models: test multiple model families (e.g., logistic regression, random forest, gradient boosting). Use identical hyperparameter search spaces per pipeline.

Reproducibility & no-peek:

• Split each dataset once into Train (80%) and Holdout (20%): holdout sealed until final evaluation.
• All preprocessing decisions for a fold must be fit only on training data inside CV (use scikit-learn Pipelines/ColumnTransformer, tools that bundle preprocessing steps like scaling, imputation, or encoding together with the model so the whole bundle gets re-fit fresh on each fold's training data instead of accidentally being fit once on the full dataset).
• Persist random seeds, environment, and code in version control.

Validation scheme:

• On Train (80%), run nested CV: outer K-fold (K=5) for model evaluation; inner K'-fold (K'=3) for hyperparameter tuning. Nested CV uses two loops instead of one because tuning your best hyperparameters and then evaluating on that same fold would give an optimistically biased score, the model effectively got to peek at what does well on that exact data. The inner loop's only job is to pick hyperparameters using its own separate mini train/validation split; the outer loop's only job is to report an honest score on data the inner loop never touched. For each outer fold, fit the full pipeline (including EDA transform logic) only on the inner training folds.
• Record out-of-fold predictions and metrics for each outer fold; average to get cross-validated performance per pipeline-model-dataset.

Evaluation metrics & analysis:

• Choose primary metric appropriate to task (AUC for classification, RMSE for regression) plus calibration and business metrics (precision@k).
• Compare Baseline vs Transformed using paired statistical tests across outer folds (e.g., paired t-test or Wilcoxon signed-rank) and report effect sizes and CIs. Paired here means comparing the two pipelines' scores fold-by-fold on the same outer folds rather than as two independent groups, since pairing cancels out fold-to-fold difficulty variation that would otherwise drown out the transform's effect; a paired t-test assumes the per-fold differences are roughly normal, while Wilcoxon signed-rank makes the same comparison without that assumption, useful with as few as 5 folds.
• Also run results aggregated across datasets to check generalization (use random-effects meta-analysis: this pools the per-dataset effect estimates into one combined estimate while allowing the transform's true effect to genuinely vary by dataset rather than assuming one single true effect underlies all of them, and it down-weights any one dataset whose result is unusually noisy).

Implementation notes:

• Use pipelines to avoid leakage (fit transformers inside CV).
• For imputation strategies that depend on target (e.g., target encoding, which replaces a categorical value with the average outcome for that category, and leaks the label into the feature if fit on the whole dataset instead of separately inside each training fold), apply within inner folds with regularization and smoothing (smoothing blends a category's own average toward the overall average when that category has few examples, so a category seen only twice doesn't get an extreme, noisy encoded value); include proper leakage controls.
• Log all artifacts (models, seeds, transforms) and use the sealed holdout only once for final confirmation.

Outcome:

• Report per-dataset and aggregated metric deltas, p-values, confidence intervals, and practical impact (e.g., change in predicted positives). If holdout confirms improvement, recommend adopting transformation with monitoring and A/B rollout.
• Illustrative positive result: transformed pipeline's mean CV AUC is 0.83 vs. baseline 0.80 across the 5 outer folds (paired t-test p=0.03), a real, statistically supported gain worth adopting. Illustrative null result: transformed pipeline's mean CV AUC is 0.801 vs. baseline 0.799 (paired t-test p=0.41): the transform isn't reliably helping, and the added pipeline complexity (an extra log/binning/imputation step to maintain in production) likely isn't worth it for that gain.

Validation set: a dataset held out from training used to compare models and tune hyperparameters. It guides model selection but influences choices, so it can cause optimistic bias if reused too much. Test set: a final, untouched dataset used only once (or very rarely) to estimate true generalization performance after all decisions are locked.

Common pitfalls and leakage sources:

• Peeking at the test set (using test metrics to choose models/hyperparams).
• Target leakage: including features derived from future information or the label (e.g., using "days_until_readmission" to predict readmission).
• Preprocessing leakage: fitting scalers, imputers, PCA, or feature selection on the full dataset instead of only the training split.
• Time-series misuse: random splits that mix past/future, leaking future information into training.
• Reusing validation repeatedly without correction: overfitting to the validation set.

Practical rules and pipeline structure to avoid issues:

• Rule 1: Never touch the test set until final evaluation.
• Rule 2: Always fit preprocessing (imputer, scaler, encoder, feature selection, PCA) only on training data; apply the fitted transformers to validation/test.
• Rule 3: Use scikit-learn Pipelines (or equivalent) so transforms and model fitting are atomic and safe inside cross-validation.
• Rule 4: For hyperparameter tuning, use cross-validation on the training set or nested CV: outer loop for performance estimate, inner loop for hyperparameter selection.
• Rule 5: For time-series, use time-aware splits (e.g., rolling/expanding window) to preserve temporal order.
• Rule 6: Put feature engineering that uses the label or aggregates across rows (e.g., group-based stats) inside the training fold computation to avoid leakage.
• Rule 7 (group splits): When records are grouped by an entity, for example every event from the same user or session, the whole group must land in only one split. A random row-level split can put 8 of a user's 10 events in train and the other 2 in test, so the model has effectively already seen that user during training and the test score is optimistically inflated. Use GroupKFold (or an equivalent grouped split) keyed on user_id or session_id so no single user's or session's rows ever appear in both train and test.
• Rule 8 (stratification): A plain random split can, by chance, land a rare class at very different rates across train/val/test, for example a 2% fraud rate in training but 0.5% in the test fold purely from sampling noise. Stratified splitting (e.g., scikit-learn's StratifiedKFold or a stratified train_test_split) preserves the proportion of each class across every split, so a rare class doesn't vanish from or get amplified in one fold by chance, and validation metrics reflect the model's actual behavior on the true class ratio.

Example pipeline (sketch):

• Split: train / validation / test (or train+CV / test)
• Within CV fold or training split:
  • Fit imputer → fit scaler/encoder → fit feature selector/PCA → train model
  • Apply same fitted transforms to validation/test
• For hyperparameter search: use GridSearchCV with Pipeline or nested CV

These practices ensure preprocessing and selection decisions are based only on training information, preventing leakage and giving reliable estimates of real-world performance.

Direct answer. Aggregate the predictions log with a single grouped query, filtering out unlabeled rows before computing the metrics, and add segment to the GROUP BY only when you need the breakdown, computing all segments in the same pass rather than re-scanning per segment.

The query.

SELECT
  DATE_TRUNC('day', event_time)          AS day,
  model_version,
  COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1) AS tp,
  COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 0) AS fp,
  COUNT(*) FILTER (WHERE predicted_label = 0 AND ground_truth_label = 1) AS fn,
  ROUND(
    COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1)::numeric
    / NULLIF(COUNT(*) FILTER (WHERE predicted_label = 1), 0), 4
  ) AS precision,
  ROUND(
    COUNT(*) FILTER (WHERE predicted_label = 1 AND ground_truth_label = 1)::numeric
    / NULLIF(COUNT(*) FILTER (WHERE ground_truth_label = 1), 0), 4
  ) AS recall
FROM predictions
WHERE ground_truth_label IS NOT NULL
  AND event_time >= NOW() - INTERVAL '30 days'
GROUP BY 1, 2
ORDER BY 1, 2;

Structured elaboration. FILTER (WHERE ...) computes each conditional count in one pass over the same grouped rows, which is both more readable and cheaper than four separate CASE WHEN sums or four subqueries; NULLIF(..., 0) turns a would-be division-by-zero into a clean NULL rather than an error when a model_version/day pair has zero predicted-positive rows. Filtering ground_truth_label IS NOT NULL up front, before the aggregation, means Postgres never even considers unlabeled rows in the FILTER counts, so precision and recall are computed strictly over the labeled subset as required.

Worked example. Say model_version v3 on a given day has 5 rows with ground truth: 3 true positives, 1 false positive, 1 false negative (and any number of true negatives, which don't enter precision or recall). Precision = 3/(3+1) = 0.75, recall = 3/(3+1) = 0.75 in this small case; the query above reproduces exactly this arithmetic per (day, model_version) group because FILTER counts are computed over the full partition before the division.

Extending to per-segment metrics without re-scanning. Add segment as a second grouping key in the same query rather than running the whole query once per segment value:

GROUP BY DATE_TRUNC('day', event_time), model_version, segment

This still costs one sequential (or index) scan of the 30-day window; Postgres computes all segment buckets in the same aggregation pass because GROUP BY fans a single scan out into multiple output groups, it does not rerun the scan per group. If segment is not already a column on predictions, join it from a small dimension table on user_id before aggregating, and add a composite index on (model_version, event_time) (or a BRIN index on event_time for a large, time-ordered table) so the 30-day filter can use an index range scan instead of a full table scan. For a table with hundreds of millions of rows, also consider a nightly pre-aggregated rollup table keyed by (day, model_version, segment) that the dashboard reads from, so the expensive full-precision scan runs once per day rather than on every dashboard refresh.

Trade-offs and pitfalls. ROUND(...::numeric, 4) avoids the classic Postgres pitfall of integer division truncating to 0 when both counts are small integers; casting to numeric (or ::float) before dividing is required. Running one query per segment value instead of grouping by segment directly is the anti-pattern to avoid: it turns an O(1)-scan aggregation into an O(number of segments)-scan one.

ROC-AUC (receiver operating characteristic AUC) measures a model’s ability to rank positives above negatives by plotting TPR vs FPR across thresholds. PR-AUC (precision–recall AUC) plots precision vs recall and emphasizes the model’s positive predictive value. When positives are rare, PR-AUC is more informative because precision directly incorporates class prevalence (it penalizes false positives heavily), while FPR can look small simply because there are many negatives.

Synthetic example:

• Dataset: 1,000 samples, 10 positives (1%), 990 negatives.
• At a chosen threshold the model returns: TP = 9, FN = 1, FP = 99, TN = 891.
  • Recall (TPR) = 9/10 = 0.90
  • FPR = 99/990 = 0.10
  • Precision = 9 / (9 + 99) ≈ 0.083

Interpretation:

• The ROC point (TPR=0.90, FPR=0.10) looks strong → contributes to a high ROC-AUC.
• Precision is very low (8.3%), so PR-AUC is poor: most predicted positives are false alarms.
• In imbalanced settings, small FPRs correspond to many false positives in absolute terms, which ROC hides but PR highlights.

Practical guidance:

• For rare-event detection, prioritize PR-AUC or Average Precision, and report precision at operational recall (e.g., precision at 80% recall).
• Also consider calibration, cost-sensitive metrics (F1, precision@k), and business impact when selecting thresholds.