SQL for Monthly KPIs | Dataford Interview Questions

Given a set of business rules and source tables, write an SQL query to join the tables, deduplicate records, and calculate monthly KPIs.

Assume the latest updated_at row represents the valid version of each transaction. Include transactions belonging to active accounts and calculate monthly metrics from the transaction timestamp.

Output

One row per month, with columns month, total_transactions, approved_transactions, approved_amount, active_customers, and approval_rate_pct
Include only months containing qualifying transactions
Order by month ascending

Schema

source_transactions

Column	Type	Description
transaction_id	VARCHAR(20)	Business transaction identifier, which may have multiple source versions
account_id	INT	Customer account associated with the transaction
transaction_ts	TIMESTAMP	Timestamp when the transaction occurred
status	VARCHAR(20)	Transaction processing status
amount	DECIMAL(12,2)	Transaction amount in Omani rials
updated_at	TIMESTAMP	Source update timestamp used to identify the latest version

customer_accounts

Column	Type	Description
account_idPK	INT	Unique customer account identifier
account_name	VARCHAR(100)	Customer or organization name
city	VARCHAR(50)	Primary account city
account_status	VARCHAR(20)	Whether the account is active or inactive

Your question is SQL for Monthly KPIs. 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