Fraud Metrics SQL Query | Dataford Interview Questions

Write a SQL query to analyze fraud cases by team and week, including joins across case, agent, and outcome tables, and calculate weekly resolution rates.

Use the provided tables and include cases even when an agent or outcome is missing. Treat an outcome with status Resolved as resolved.

Output

One row per team and week, using the case opened date for the week.
Columns: team_name, week_start, total_cases, resolved_cases, and resolution_rate_pct.
Include all cases, label missing teams as Unassigned, calculate the rate as a percentage rounded to two decimal places, and order by week_start, then team_name.

Schema

fraud_cases

Column	Type	Description
case_idPK	INT	Unique fraud case identifier
agent_id	INT	Agent assigned to the case
opened_at	TIMESTAMP	Timestamp when the case was opened
priority	VARCHAR(20)	Operational priority of the case

agents

Column	Type	Description
agent_idPK	INT	Unique agent identifier
agent_name	VARCHAR(100)	Agent display name
team_name	VARCHAR(100)	Team assigned to the agent

outcomes

Column	Type	Description
outcome_idPK	INT	Unique outcome identifier
case_id	INT	Fraud case associated with the outcome
outcome_status	VARCHAR(30)	Final or current case outcome
resolved_at	TIMESTAMP	Timestamp when the case was resolved

Your question is Fraud Metrics SQL Query. Start with the requirements and the three 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