HTTP and JSON Data Processing | Dataford Interview Questions

Describe how you parse HTTP responses and process JSON data in your software.

For this SQL exercise, response payloads are stored as PostgreSQL JSONB. Write a query that returns parsed entities from successful responses and handles missing JSON fields consistently.

Output

One row per entity in a successful response, with request_id, endpoint, received_at, item_position, entity_id, entity_name, entity_type, and normalized_status.
Include only HTTP status codes from 200 through 299, normalize missing or unrecognized statuses to unknown, and order by received_at, request_id, and item_position.

Schema

api_requests

Column	Type	Description
request_idPK	INT	Unique identifier for the outgoing HTTP request
endpoint	VARCHAR(120)	Requested API endpoint
requested_at	TIMESTAMP	Time when the request was sent
client_name	VARCHAR(80)	Optional name of the calling client

http_responses

Column	Type	Description
response_idPK	INT	Unique identifier for the HTTP response
request_id	INT	Identifier of the related request
status_code	INT	HTTP response status code
received_at	TIMESTAMP	Time when the response was received
response_body	JSONB	Decoded HTTP response body

Your question is HTTP and JSON Data Processing. 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