| Training dataset length: 9074 | |
| Training Example 0: | |
| {'db_id': 'movie_platform', 'question': 'Name movie titles released in year 1945. Sort the listing by the descending order of movie popularity.', 'evidence': 'released in the year 1945 refers to movie_release_year = 1945;', 'target_sql': 'SELECT movie_title FROM movies WHERE movie_release_year = 1945 ORDER BY movie_popularity DESC LIMIT 1', 'db_schema_T': 'CREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)', 'db_schema': 'CREATE TABLE "lists"\n(\n user_id INTEGER\n references lists_users (user_id),\n list_id INTEGER not null\n primary key,\n list_title TEXT,\n list_movie_number INTEGER,\n list_update_timestamp_utc TEXT,\n list_creation_timestamp_utc TEXT,\n list_followers INTEGER,\n list_url TEXT,\n list_comments INTEGER,\n list_description TEXT,\n list_cover_image_url TEXT,\n list_first_image_url TEXT,\n list_second_image_url TEXT,\n list_third_image_url TEXT\n)\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\nCREATE TABLE "ratings_users"\n(\n user_id INTEGER\n references lists_users (user_id),\n rating_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER\n)\nCREATE TABLE lists_users\n(\n user_id INTEGER not null ,\n list_id INTEGER not null ,\n list_update_date_utc TEXT,\n list_creation_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial TEXT,\n user_has_payment_method TEXT,\n primary key (user_id, list_id),\n foreign key (list_id) references lists(list_id),\n foreign key (user_id) references lists(user_id)\n)\nCREATE TABLE ratings\n(\n movie_id INTEGER,\n rating_id INTEGER,\n rating_url TEXT,\n rating_score INTEGER,\n rating_timestamp_utc TEXT,\n critic TEXT,\n critic_likes INTEGER,\n critic_comments INTEGER,\n user_id INTEGER,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER,\n foreign key (movie_id) references movies(movie_id),\n foreign key (user_id) references lists_users(user_id),\n foreign key (rating_id) references ratings(rating_id),\n foreign key (user_id) references ratings_users(user_id)\n)', 'difficulty': 'na', 'question_id': 0, '__index_level_0__': 0, 'db_schema_TC': 'CREATE TABLE `movies`\n(\n\n movie_title TEXT, \n movie_release_year INTEGER, \n movie_title_language TEXT, \n movie_popularity INTEGER\n)', 'prompt': [{'content': 'You are a helpful AI Assistant that provides well-reasoned and detailed responses. You first think about the reasoning process as an internal monologue and then provide the user with the answer. Respond in the following format: <think>\n...\n</think>\n<answer>\n...\n</answer>', 'role': 'system'}, {'content': 'Answer the following question with the SQL code. Use the piece of evidence and base your answer on the database schema. Given the question, the evidence and the database schema, return in the <answer> tags only the SQL script that addresses the question.\nQuestion:\nName movie titles released in year 1945. Sort the listing by the descending order of movie popularity.\n\nEvidence:\nreleased in the year 1945 refers to movie_release_year = 1945;\n\nDatabase Schema:\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\n\nReturn only the SQL script enclosed in <answer> tags.', 'role': 'user'}]} | |
| -------------------------------------------------- | |
| Training Example 1: | |
| {'db_id': 'movie_platform', 'question': 'State the most popular movie? When was it released and who is the director for the movie?', 'evidence': 'most popular movie refers to MAX(movie_popularity); when it was released refers to movie_release_year; director for the movie refers to director_name;', 'target_sql': 'SELECT movie_title, movie_release_year, director_name FROM movies ORDER BY movie_popularity DESC LIMIT 1 ', 'db_schema_T': 'CREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)', 'db_schema': 'CREATE TABLE "lists"\n(\n user_id INTEGER\n references lists_users (user_id),\n list_id INTEGER not null\n primary key,\n list_title TEXT,\n list_movie_number INTEGER,\n list_update_timestamp_utc TEXT,\n list_creation_timestamp_utc TEXT,\n list_followers INTEGER,\n list_url TEXT,\n list_comments INTEGER,\n list_description TEXT,\n list_cover_image_url TEXT,\n list_first_image_url TEXT,\n list_second_image_url TEXT,\n list_third_image_url TEXT\n)\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\nCREATE TABLE "ratings_users"\n(\n user_id INTEGER\n references lists_users (user_id),\n rating_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER\n)\nCREATE TABLE lists_users\n(\n user_id INTEGER not null ,\n list_id INTEGER not null ,\n list_update_date_utc TEXT,\n list_creation_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial TEXT,\n user_has_payment_method TEXT,\n primary key (user_id, list_id),\n foreign key (list_id) references lists(list_id),\n foreign key (user_id) references lists(user_id)\n)\nCREATE TABLE ratings\n(\n movie_id INTEGER,\n rating_id INTEGER,\n rating_url TEXT,\n rating_score INTEGER,\n rating_timestamp_utc TEXT,\n critic TEXT,\n critic_likes INTEGER,\n critic_comments INTEGER,\n user_id INTEGER,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER,\n foreign key (movie_id) references movies(movie_id),\n foreign key (user_id) references lists_users(user_id),\n foreign key (rating_id) references ratings(rating_id),\n foreign key (user_id) references ratings_users(user_id)\n)', 'difficulty': 'na', 'question_id': 1, '__index_level_0__': 1, 'db_schema_TC': 'CREATE TABLE `movies`\n(\n\n movie_title TEXT, \n movie_release_year INTEGER, \n movie_title_language TEXT, \n movie_popularity INTEGER, \n director_name TEXT\n)', 'prompt': [{'content': 'You are a helpful AI Assistant that provides well-reasoned and detailed responses. You first think about the reasoning process as an internal monologue and then provide the user with the answer. Respond in the following format: <think>\n...\n</think>\n<answer>\n...\n</answer>', 'role': 'system'}, {'content': 'Answer the following question with the SQL code. Use the piece of evidence and base your answer on the database schema. Given the question, the evidence and the database schema, return in the <answer> tags only the SQL script that addresses the question.\nQuestion:\nState the most popular movie? When was it released and who is the director for the movie?\n\nEvidence:\nmost popular movie refers to MAX(movie_popularity); when it was released refers to movie_release_year; director for the movie refers to director_name;\n\nDatabase Schema:\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\n\nReturn only the SQL script enclosed in <answer> tags.', 'role': 'user'}]} | |
| -------------------------------------------------- | |
| Training Example 2: | |
| {'db_id': 'movie_platform', 'question': 'What is the name of the longest movie title? When was it released?', 'evidence': 'longest movie title refers to MAX(LENGTH(movie_title)); when it was released refers to movie_release_year;', 'target_sql': 'SELECT movie_title, movie_release_year FROM movies ORDER BY LENGTH(movie_popularity) DESC LIMIT 1', 'db_schema_T': 'CREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)', 'db_schema': 'CREATE TABLE "lists"\n(\n user_id INTEGER\n references lists_users (user_id),\n list_id INTEGER not null\n primary key,\n list_title TEXT,\n list_movie_number INTEGER,\n list_update_timestamp_utc TEXT,\n list_creation_timestamp_utc TEXT,\n list_followers INTEGER,\n list_url TEXT,\n list_comments INTEGER,\n list_description TEXT,\n list_cover_image_url TEXT,\n list_first_image_url TEXT,\n list_second_image_url TEXT,\n list_third_image_url TEXT\n)\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\nCREATE TABLE "ratings_users"\n(\n user_id INTEGER\n references lists_users (user_id),\n rating_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER\n)\nCREATE TABLE lists_users\n(\n user_id INTEGER not null ,\n list_id INTEGER not null ,\n list_update_date_utc TEXT,\n list_creation_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial TEXT,\n user_has_payment_method TEXT,\n primary key (user_id, list_id),\n foreign key (list_id) references lists(list_id),\n foreign key (user_id) references lists(user_id)\n)\nCREATE TABLE ratings\n(\n movie_id INTEGER,\n rating_id INTEGER,\n rating_url TEXT,\n rating_score INTEGER,\n rating_timestamp_utc TEXT,\n critic TEXT,\n critic_likes INTEGER,\n critic_comments INTEGER,\n user_id INTEGER,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER,\n foreign key (movie_id) references movies(movie_id),\n foreign key (user_id) references lists_users(user_id),\n foreign key (rating_id) references ratings(rating_id),\n foreign key (user_id) references ratings_users(user_id)\n)', 'difficulty': 'na', 'question_id': 2, '__index_level_0__': 2, 'db_schema_TC': 'CREATE TABLE `movies`\n(\n\n movie_title TEXT, \n movie_release_year INTEGER, \n movie_title_language TEXT, \n movie_popularity INTEGER\n)', 'prompt': [{'content': 'You are a helpful AI Assistant that provides well-reasoned and detailed responses. You first think about the reasoning process as an internal monologue and then provide the user with the answer. Respond in the following format: <think>\n...\n</think>\n<answer>\n...\n</answer>', 'role': 'system'}, {'content': 'Answer the following question with the SQL code. Use the piece of evidence and base your answer on the database schema. Given the question, the evidence and the database schema, return in the <answer> tags only the SQL script that addresses the question.\nQuestion:\nWhat is the name of the longest movie title? When was it released?\n\nEvidence:\nlongest movie title refers to MAX(LENGTH(movie_title)); when it was released refers to movie_release_year;\n\nDatabase Schema:\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\n\nReturn only the SQL script enclosed in <answer> tags.', 'role': 'user'}]} | |
| -------------------------------------------------- | |
| Training Example 3: | |
| {'db_id': 'movie_platform', 'question': 'Name the movie with the most ratings.', 'evidence': 'movie with the most rating refers to MAX(SUM(rating_score));', 'target_sql': 'SELECT movie_title FROM movies GROUP BY movie_title ORDER BY COUNT(movie_title) DESC LIMIT 1', 'db_schema_T': 'CREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)', 'db_schema': 'CREATE TABLE "lists"\n(\n user_id INTEGER\n references lists_users (user_id),\n list_id INTEGER not null\n primary key,\n list_title TEXT,\n list_movie_number INTEGER,\n list_update_timestamp_utc TEXT,\n list_creation_timestamp_utc TEXT,\n list_followers INTEGER,\n list_url TEXT,\n list_comments INTEGER,\n list_description TEXT,\n list_cover_image_url TEXT,\n list_first_image_url TEXT,\n list_second_image_url TEXT,\n list_third_image_url TEXT\n)\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\nCREATE TABLE "ratings_users"\n(\n user_id INTEGER\n references lists_users (user_id),\n rating_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER\n)\nCREATE TABLE lists_users\n(\n user_id INTEGER not null ,\n list_id INTEGER not null ,\n list_update_date_utc TEXT,\n list_creation_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial TEXT,\n user_has_payment_method TEXT,\n primary key (user_id, list_id),\n foreign key (list_id) references lists(list_id),\n foreign key (user_id) references lists(user_id)\n)\nCREATE TABLE ratings\n(\n movie_id INTEGER,\n rating_id INTEGER,\n rating_url TEXT,\n rating_score INTEGER,\n rating_timestamp_utc TEXT,\n critic TEXT,\n critic_likes INTEGER,\n critic_comments INTEGER,\n user_id INTEGER,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER,\n foreign key (movie_id) references movies(movie_id),\n foreign key (user_id) references lists_users(user_id),\n foreign key (rating_id) references ratings(rating_id),\n foreign key (user_id) references ratings_users(user_id)\n)', 'difficulty': 'na', 'question_id': 3, '__index_level_0__': 3, 'db_schema_TC': 'CREATE TABLE `movies`\n(\n\n movie_title TEXT, \n movie_title_language TEXT\n)', 'prompt': [{'content': 'You are a helpful AI Assistant that provides well-reasoned and detailed responses. You first think about the reasoning process as an internal monologue and then provide the user with the answer. Respond in the following format: <think>\n...\n</think>\n<answer>\n...\n</answer>', 'role': 'system'}, {'content': 'Answer the following question with the SQL code. Use the piece of evidence and base your answer on the database schema. Given the question, the evidence and the database schema, return in the <answer> tags only the SQL script that addresses the question.\nQuestion:\nName the movie with the most ratings.\n\nEvidence:\nmovie with the most rating refers to MAX(SUM(rating_score));\n\nDatabase Schema:\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\n\nReturn only the SQL script enclosed in <answer> tags.', 'role': 'user'}]} | |
| -------------------------------------------------- | |
| Training Example 4: | |
| {'db_id': 'movie_platform', 'question': 'What is the average number of Mubi users who love movies directed by Stanley Kubrick?', 'evidence': 'average = AVG(movie_popularity); number of Mubi users who loves the movie refers to movie_popularity;', 'target_sql': "SELECT AVG(movie_popularity) FROM movies WHERE director_name = 'Stanley Kubrick'", 'db_schema_T': 'CREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)', 'db_schema': 'CREATE TABLE "lists"\n(\n user_id INTEGER\n references lists_users (user_id),\n list_id INTEGER not null\n primary key,\n list_title TEXT,\n list_movie_number INTEGER,\n list_update_timestamp_utc TEXT,\n list_creation_timestamp_utc TEXT,\n list_followers INTEGER,\n list_url TEXT,\n list_comments INTEGER,\n list_description TEXT,\n list_cover_image_url TEXT,\n list_first_image_url TEXT,\n list_second_image_url TEXT,\n list_third_image_url TEXT\n)\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\nCREATE TABLE "ratings_users"\n(\n user_id INTEGER\n references lists_users (user_id),\n rating_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER\n)\nCREATE TABLE lists_users\n(\n user_id INTEGER not null ,\n list_id INTEGER not null ,\n list_update_date_utc TEXT,\n list_creation_date_utc TEXT,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_avatar_image_url TEXT,\n user_cover_image_url TEXT,\n user_eligible_for_trial TEXT,\n user_has_payment_method TEXT,\n primary key (user_id, list_id),\n foreign key (list_id) references lists(list_id),\n foreign key (user_id) references lists(user_id)\n)\nCREATE TABLE ratings\n(\n movie_id INTEGER,\n rating_id INTEGER,\n rating_url TEXT,\n rating_score INTEGER,\n rating_timestamp_utc TEXT,\n critic TEXT,\n critic_likes INTEGER,\n critic_comments INTEGER,\n user_id INTEGER,\n user_trialist INTEGER,\n user_subscriber INTEGER,\n user_eligible_for_trial INTEGER,\n user_has_payment_method INTEGER,\n foreign key (movie_id) references movies(movie_id),\n foreign key (user_id) references lists_users(user_id),\n foreign key (rating_id) references ratings(rating_id),\n foreign key (user_id) references ratings_users(user_id)\n)', 'difficulty': 'na', 'question_id': 4, '__index_level_0__': 4, 'db_schema_TC': 'CREATE TABLE `movies`\n(\n\n movie_popularity INTEGER, \n director_name TEXT\n)', 'prompt': [{'content': 'You are a helpful AI Assistant that provides well-reasoned and detailed responses. You first think about the reasoning process as an internal monologue and then provide the user with the answer. Respond in the following format: <think>\n...\n</think>\n<answer>\n...\n</answer>', 'role': 'system'}, {'content': 'Answer the following question with the SQL code. Use the piece of evidence and base your answer on the database schema. Given the question, the evidence and the database schema, return in the <answer> tags only the SQL script that addresses the question.\nQuestion:\nWhat is the average number of Mubi users who love movies directed by Stanley Kubrick?\n\nEvidence:\naverage = AVG(movie_popularity); number of Mubi users who loves the movie refers to movie_popularity;\n\nDatabase Schema:\nCREATE TABLE "movies"\n(\n movie_id INTEGER not null\n primary key,\n movie_title TEXT,\n movie_release_year INTEGER,\n movie_url TEXT,\n movie_title_language TEXT,\n movie_popularity INTEGER,\n movie_image_url TEXT,\n director_id TEXT,\n director_name TEXT,\n director_url TEXT\n)\n\nReturn only the SQL script enclosed in <answer> tags.', 'role': 'user'}]} | |
| -------------------------------------------------- | |