SQL KPI Measurement With Python | Dataford Interview Questions

How would you measure KPIs using SQL queries and Python data labeling?

Assume Python-generated transaction labels are stored in the provided label table. Write a query that produces monthly KPI metrics by user risk tier. Ignore transactions with a NULL timestamp and users with a NULL risk tier.

Output

One row per month and risk tier, with month_start, risk_tier, active_users, transaction_count, completed_volume, success_rate_pct, suspicious_rate_pct, label_coverage_pct, and previous_success_rate_pct.
Label-based rates use only non-NULL labels. Rows with NULL timestamps or NULL risk tiers are excluded.
Order by month_start, then risk_tier.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
risk_tier	VARCHAR(20)	User risk classification

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	User associated with the transaction
transaction_at	TIMESTAMP	Transaction timestamp
amount	NUMERIC(14,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status
channel	VARCHAR(40)	Transaction channel

transaction_labels

Column	Type	Description
label_idPK	INT	Unique label record identifier
transaction_id	INT	Labeled transaction identifier
label	VARCHAR(20)	Python-generated transaction label
labeled_at	TIMESTAMP	Timestamp when the label was produced

Your question is SQL KPI Measurement With Python. 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