Retrieve Paginated JSON Movies | Dataford Interview Questions

Given a list of movie titles from a URL, retrieve all pages of JSON data and return a list of movies.

The URL requests and captured JSON responses are stored in the provided tables. Return only movies whose titles were requested, deduplicate repeated results, and retain the earliest page containing each movie.

Output

One row per unique matching movie, with columns movie_title, release_year, imdb_id, and first_page.
Exclude movies not present in the requested title list.
Order by movie_title, release_year, and imdb_id ascending.

Schema

movie_searches

Column	Type	Description
search_idPK	INT	Identifier for one movie search request
source_url	VARCHAR(500)	URL used to retrieve the movie data

search_titles

Column	Type	Description
search_id	INT	References the movie search request
requested_title	VARCHAR(200)	Movie title requested by the search
title_position	INT	Position of the title in the original request

api_pages

Column	Type	Description
search_id	INT	References the movie search request
page_numberPK	INT	Page number returned by the API
total_pages	INT	Total number of pages for the search
response_json	JSONB	JSON response containing a movies array

Your question is Retrieve Paginated JSON Movies. Start with the requirements and the three 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