Advanced SQL for Fraud Metrics | Dataford Interview Questions

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

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

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_amount	DECIMAL(12,2)	Transaction amount
actual_fraud	BOOLEAN	Confirmed fraud label; NULL means not labeled

fraud_scores

Column	Type	Description
score_idPK	INT	Unique model score identifier
transaction_id	INT	Transaction scored by the model
model_name	VARCHAR(50)	Fraud model name
fraud_score	DECIMAL(4,2)	Model fraud probability score

Your question is Advanced SQL for Fraud Metrics. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem