Database Trade-Offs for Detection | Dataford Interview Questions

Explain the trade-offs between different database architectures in the context of a financial crime detection system.

Write a SQL query that summarizes completed detection runs for each architecture and exposes comparable operational trade-offs through latency, alerts, cost, and throughput metrics.

Output

One row per architecture, including architectures with no completed runs.
Columns: architecture_name, run_count, avg_latency_ms, total_alerts, avg_cost_usd, and avg_throughput_tx_per_sec.
Sort by average latency ascending, place missing latency values last, then sort by architecture name.

Schema

architectures

Column	Type	Description
architecture_idPK	INT	Unique architecture identifier
architecture_name	VARCHAR(100)	Architecture name
architecture_type	VARCHAR(50)	Broad database architecture category

detection_runs

Column	Type	Description
run_idPK	INT	Unique detection run identifier
architecture_id	INT	Referenced architecture
status	VARCHAR(20)	Run status
latency_ms	DECIMAL(10,2)	End-to-end detection latency in milliseconds
alert_count	INT	Number of alerts generated
cost_usd	DECIMAL(10,2)	Estimated run cost in US dollars
transactions_processed	BIGINT	Number of transactions processed
runtime_seconds	DECIMAL(10,2)	Run duration in seconds

Your question is Database Trade-Offs for 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