Advanced SQL for Fraud Metrics

Tell me about a time you worked on SQL questions involving if-else logic, window functions, and fraud detection KPIs like recall and precision.

Use the provided transaction and fraud score data to calculate fraud KPIs for every available model threshold. Select the best threshold for each model using F1 score, with precision and recall as supporting metrics.

Output

1. One row per model's best threshold.
2. Columns: model_name, threshold, precision, recall, f1_score, and threshold_rank.
3. Include only the top-ranked threshold per model, ordered by model_name.

Output details

• Use each distinct non-NULL fraud_score as a model-specific threshold candidate.
• Exclude records with NULL fraud_score or NULL actual_fraud.
• Treat fraud_score >= threshold as the inclusive fraud-prediction condition.
• Round precision, recall, and f1_score to 4 decimal places; return NULL when a denominator is zero.
• Break equal F1 scores by choosing the higher threshold, and sort models ascending by model_name.