Querying Data via API | Dataford Interview Questions

Given an API and dataset, how would you retrieve specific data using that API?

Assume API request metadata and dataset records are stored in the provided tables. Write a PostgreSQL query that returns only records requested by ready API requests and currently marked active.

Output

One row per successfully matched API request and dataset record.
Columns: request_id, dataset_id, record_key, record_value, and requested_at.
Sort by requested_at descending, then request_id ascending.

Schema

api_requests

Column	Type	Description
request_idPK	INT	Unique API request identifier
dataset_id	INT	Identifier of the requested dataset
requested_key	VARCHAR(30)	Record key requested through the API
request_status	VARCHAR(20)	Processing status of the API request
requested_at	TIMESTAMP	Time when the request was created

dataset_records

Column	Type	Description
record_idPK	INT	Unique dataset record identifier
dataset_id	INT	Identifier of the dataset containing the record
record_key	VARCHAR(30)	Key used to identify the record
record_value	VARCHAR(100)	Value returned for the record
is_active	BOOLEAN	Whether the record is currently available

Your question is Querying Data via API. 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