SQL KPI Measurement With Python

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

1. 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.
2. Label-based rates use only non-NULL labels. Rows with NULL timestamps or NULL risk tiers are excluded.
3. Order by month_start, then risk_tier.

Output details

• Sort month_start ascending, then risk_tier ascending.
• Round success_rate_pct, suspicious_rate_pct, and label_coverage_pct to two decimal places.
• Calculate success_rate_pct as completed transactions divided by all transactions, returning NULL only when the denominator is zero.
• Calculate suspicious_rate_pct as suspicious labeled transactions divided by non-NULL labeled transactions, returning NULL when no labels exist.
• Return 0 for completed_volume when its completed-amount sum is NULL.
• Set previous_success_rate_pct to the chronologically previous available month for the same risk_tier, or NULL when none exists.