Extract Substrings From Logs | Dataford Interview Questions

Write a query to perform string manipulation to extract specific substrings from a messy log table.

Use messy_logs and log_sources. Extract the policy number, claim ID, and event code when present, while retaining rows with missing or malformed values.

Output

One row per log event with columns event_id, source_name, policy_number, claim_id, and event_code.
Include unmatched source records and missing extracted values as NULL.
Order by received_at, then event_id.

Schema

messy_logs

Column	Type	Description
event_idPK	INT	Unique log event identifier
source_id	INT	Identifier of the system that generated the log
received_at	TIMESTAMP	Timestamp when the log was received
raw_message	TEXT	Unstructured log message

log_sources

Column	Type	Description
source_idPK	INT	Unique source identifier
source_name	VARCHAR(100)	Human-readable source name

Your question is Extract Substrings From 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.

Problem