Database schema:

CREATE TABLE lists (
	user_id INTEGER -- example: ['88260493', '45204418'],
	list_id INTEGER NOT NULL -- example: ['2', '1'],
	list_title TEXT -- example: ['Films that made your kid sister cry', 'Headscratchers'],
	list_movie_number INTEGER -- example: ['5', '3'],
	list_update_timestamp_utc TEXT, 
	list_creation_timestamp_utc TEXT -- example: ['2009-11-11 00:02:21', '2009-11-11 00:05:11'],
	list_followers INTEGER -- example: ['5', '1'],
	list_url TEXT -- example: ['http://mubi.com/lists/headscratchers', 'http://mubi.com/lists/films-that-made-your-kid-sis'],
	list_comments INTEGER -- example: ['2', '3'],
	list_description TEXT -- example: ['<p>Films that need at least two viewings to really', '<p>Don’t be such a baby!!</p>\n<p><strong>bold</str'],
	list_cover_image_url TEXT -- example: ['https://assets.mubicdn.net/images/film/3822/image-', 'https://assets.mubicdn.net/images/film/4343/image-'],
	list_first_image_url TEXT -- example: ['https://assets.mubicdn.net/images/film/3822/image-', 'https://assets.mubicdn.net/images/film/4343/image-'],
	list_second_image_url TEXT -- example: ['https://assets.mubicdn.net/images/film/159/image-w', 'https://assets.mubicdn.net/images/film/506/image-w'],
	list_third_image_url TEXT -- example: ['https://assets.mubicdn.net/images/film/485/image-w', 'https://assets.mubicdn.net/images/film/142/image-w'],
	PRIMARY KEY (list_id), 
	CONSTRAINT fk_lists_lists_users FOREIGN KEY(user_id) REFERENCES lists_users (user_id)
)


CREATE TABLE lists_users (
	user_id INTEGER NOT NULL -- example: ['15264', '2385'],
	list_id INTEGER NOT NULL -- example: ['192287', '192313'],
	list_update_date_utc TEXT, 
	list_creation_date_utc TEXT -- example: ['2010-01-30', '2009-12-18'],
	user_trialist INTEGER -- example: ['0', '1'],
	user_subscriber INTEGER -- example: ['0', '1'],
	user_avatar_image_url TEXT -- example: ['https://graph.facebook.com/754931657/picture?width', 'https://assets.mubicdn.net/images/avatars/74983/im'],
	user_cover_image_url TEXT -- example: ['https://assets.mubicdn.net/images/cover_images/127', 'https://assets.mubicdn.net/images/cover_images/191'],
	user_eligible_for_trial TEXT -- example: ['0', '1'],
	user_has_payment_method TEXT -- example: ['0', '1'],
	PRIMARY KEY (user_id, list_id), 
	CONSTRAINT fk_lists_users_lists FOREIGN KEY(list_id) REFERENCES lists (list_id),
	CONSTRAINT fk_lists_users_lists FOREIGN KEY(user_id) REFERENCES lists (user_id)
)


CREATE TABLE movies (
	movie_id INTEGER NOT NULL -- example: ['2', '1'],
	movie_title TEXT -- example: ['Elementary Particles', 'La Antena'],
	movie_release_year INTEGER -- example: ['2006', '2007'],
	movie_url TEXT -- example: ['http://mubi.com/films/la-antena', 'http://mubi.com/films/elementary-particles'],
	movie_title_language TEXT -- example: ['en'],
	movie_popularity INTEGER -- example: ['23', '105'],
	movie_image_url TEXT -- example: ['https://images.mubicdn.net/images/film/1/cache-792', 'https://images.mubicdn.net/images/film/2/cache-512'],
	director_id TEXT -- example: ['131', '73'],
	director_name TEXT -- example: ['Oskar Roehler', 'Esteban Sapir'],
	director_url TEXT -- example: ['http://mubi.com/cast/esteban-sapir', 'http://mubi.com/cast/oskar-roehler'],
	PRIMARY KEY (movie_id)
)


CREATE TABLE ratings (
	movie_id INTEGER -- example: ['1066', '1067'],
	rating_id INTEGER -- example: ['15610495', '10704606'],
	rating_url TEXT -- example: ['http://mubi.com/films/pavee-lackeen-the-traveller-'],
	rating_score INTEGER -- example: ['2', '3'],
	rating_timestamp_utc TEXT -- example: ['2017-06-10 12:38:33', '2014-08-15 23:42:31'],
	critic TEXT -- example: ['Splendid debut. Whatever happened to Amma Asante?', 'I am a bit disappointed by this documentary film I'],
	critic_likes INTEGER -- example: ['0', '1'],
	critic_comments INTEGER -- example: ['0', '2'],
	user_id INTEGER -- example: ['85981819', '41579158'],
	user_trialist INTEGER -- example: ['0', '1'],
	user_subscriber INTEGER -- example: ['0', '1'],
	user_eligible_for_trial INTEGER -- example: ['0', '1'],
	user_has_payment_method INTEGER -- example: ['0', '1'],
	CONSTRAINT fk_ratings_movies FOREIGN KEY(movie_id) REFERENCES movies (movie_id),
	CONSTRAINT fk_ratings_lists_users FOREIGN KEY(user_id) REFERENCES lists_users (user_id),
	CONSTRAINT fk_ratings_ratings FOREIGN KEY(rating_id) REFERENCES ratings (rating_id),
	CONSTRAINT fk_ratings_ratings_users FOREIGN KEY(user_id) REFERENCES ratings_users (user_id)
)


CREATE TABLE ratings_users (
	user_id INTEGER -- example: ['68654088', '41579158'],
	rating_date_utc TEXT -- example: ['2012-10-02', '2017-06-10'],
	user_trialist INTEGER -- example: ['0', '1'],
	user_subscriber INTEGER -- example: ['0', '1'],
	user_avatar_image_url TEXT -- example: ['https://assets.mubicdn.net/images/avatars/74283/im', 'https://assets.mubicdn.net/images/avatars/27714/im'],
	user_cover_image_url TEXT -- example: ['https://assets.mubicdn.net/images/cover_images/149', 'https://assets.mubicdn.net/images/cover_images/378'],
	user_eligible_for_trial INTEGER -- example: ['0', '1'],
	user_has_payment_method INTEGER -- example: ['0', '1'],
	CONSTRAINT fk_ratings_users_lists_users FOREIGN KEY(user_id) REFERENCES lists_users (user_id)
)

Question: How many movies have a popularity of more than 400 but less than 500? Indicate the name of the movies and the highest rating score each movie has received.
Evidence: popularity of more than 400 but less than 500 refers to movie_popularity BETWEEN 400 AND 500; highest rating score refer to MAX(rating_score)



SQL:
SELECT T1.movie_title, MAX(T2.rating_score) FROM movies AS T1 INNER JOIN ratings AS T2 ON T1.movie_id = T2.movie_id WHERE T1.movie_popularity BETWEEN 400 AND 500 GROUP BY T1.movie_title