Extract KPIs from Unstructured Logs | Dataford Interview Questions

Write a script to parse unstructured log data and extract key performance indicators for a software release.

Use the provided release and log tables. Extract status codes and latency values from log messages, treating missing or malformed values as NULL.

Output

One row per release, including releases with no matching logs.
Columns: release_id, version, total_events, successful_events, error_events, error_rate_pct, avg_latency_ms, and p95_latency_ms.
Count 2xx and 3xx statuses as successful, 5xx statuses as errors, and order by release_id ascending.

software_releases

Column	Type	Description
release_idPK	INT	Unique release identifier
version	VARCHAR(30)	Release version label
release_date	DATE	Date the release became available

release_logs

Column	Type	Description
log_idPK	INT	Unique log record identifier
release_id	INT	Release associated with the log
logged_at	TIMESTAMP	Time the log event occurred
log_message	TEXT	Unstructured event log text

Your question is Extract KPIs from Unstructured Logs. 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.