SQL or Python Fraud Anomaly Detection | Dataford Interview Questions

Given a sample dataset of loan applications, how would you identify anomalies or potential fraudulent behavior using SQL or Python for Upstart?

Write a PostgreSQL query that flags applications with at least two anomaly indicators. Consider repeated applications by an applicant, devices or IP addresses shared by multiple applicants, and substantial differences between stated and application income.

Output

One row per flagged application, including application_id, applicant_id, submitted_at, anomaly_score, and anomaly_reasons.
Include only applications with an anomaly_score of at least 2.
Order by anomaly_score descending, then submitted_at ascending, and application_id ascending.

Schema

loan_applications

Column	Type	Description
application_idPK	INTEGER	Unique loan application identifier
applicant_id	INTEGER	Applicant associated with the application
submitted_at	TIMESTAMP	Application submission timestamp
annual_income	NUMERIC(12,2)	Annual income entered on the application
device_id	VARCHAR(40)	Device identifier observed at submission
ip_address	VARCHAR(45)	IP address observed at submission
status	VARCHAR(40)	Current application status

applicant_profiles

Column	Type	Description
applicant_idPK	INTEGER	Unique applicant identifier
stated_income	NUMERIC(12,2)	Income recorded in the applicant profile

Your question is SQL or Python Fraud Anomaly Detection. 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