SQL Analysis on JSON Data | Dataford Interview Questions

Ingest JSON documents into a SQLite database, and then answer an analytical question using SQL; what additional data would you need for further analysis?

Use the supplied PostgreSQL tables to represent the ingestion and analysis task. Extract document status, source, and amount from the JSON payload, then identify additional data that would support more advanced analysis.

Output

One row per registered source, including sources with no completed documents.
Columns: source_id, source_name, completed_documents, and total_amount.
Include only completed documents, treat missing amounts as zero for the total, and order by source_id ascending.

Schema

json_documents

Column	Type	Description
document_idPK	INT	Unique ingested document identifier
payload	JSONB	Raw JSON document
ingested_at	TIMESTAMPTZ	Timestamp when the document was ingested

source_registry

Column	Type	Description
source_idPK	INT	Registered source identifier
source_name	VARCHAR(100)	Human-readable source name
source_system	VARCHAR(100)	Originating system name
region	VARCHAR(50)	Operational region of the source

Your question is SQL Analysis on JSON Data. 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