Database: cookbook
Question: Which ingredient appeared the most in recipes? Calculate its amount of appearance in percentage.
Evidence: ingredient appeared the most in recipes refers to MAX(COUNT(ingredient_id)); calculation = MULTIPLY(DIVIDE(COUNT(MAX(ingredient_id)), COUNT(ingredient_id)), 100)
SQL: SELECT T1.name, CAST(COUNT(T2.ingredient_id) AS FLOAT) * 100 / ( SELECT COUNT(T2.ingredient_id) FROM Ingredient AS T1 INNER JOIN Quantity AS T2 ON T2.ingredient_id = T1.ingredient_id ) AS "percentage" FROM Ingredient AS T1 INNER JOIN Quantity AS T2 ON T2.ingredient_id = T1.ingredient_id GROUP BY T2.ingredient_id ORDER BY COUNT(T2.ingredient_id) DESC LIMIT 1

Database: video_games
Question: How many more games were sold on game platform ID 50 than on game platform ID 51 in region ID 1?
Evidence: result = subtract(sum(num_sales where game_platform_id = 50), sum(num_sales where game_platform_id = 51))
SQL: SELECT (SUM(CASE WHEN T.game_platform_id = 50 THEN T.num_sales ELSE 0 END) - SUM(CASE WHEN T.game_platform_id = 51 THEN T.num_sales ELSE 0 END)) * 100000 AS nums FROM region_sales AS T WHERE T.region_id = 1

Database: restaurant
Question: Please list all of the street names in Clayton City.
SQL: SELECT street_name FROM location WHERE city = 'Clayton'

Database: retail_world
Question: Provide the products list which were ordered in 1996 by the company in Norway.
Evidence: ordered in 1996 refers to year(OrderDate) = 1996; in Norway refers to Country = 'Norway'
SQL: SELECT T4.ProductName FROM Customers AS T1 INNER JOIN Orders AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN `Order Details` AS T3 ON T2.OrderID = T3.OrderID INNER JOIN Products AS T4 ON T3.ProductID = T4.ProductID WHERE T1.Country = 'Norway' AND STRFTIME('%Y', T2.OrderDate) = '1996'

Database: movie_3
Question: List down all film titles starred by Jane Jackman.
SQL: SELECT T1.title FROM film AS T1 INNER JOIN film_actor AS T2 ON T1.film_id = T2.film_id INNER JOIN actor AS T3 ON T2.actor_id = T3.actor_id WHERE T3.first_name = 'JANE' AND T3.last_name = 'JACKMAN'

Database: olympics
Question: How many female athletes are from the Philippines?
Evidence: female athletes refer to id where gender = 'F'; from the Philippines region_name = 'Philippines';
SQL: SELECT COUNT(T2.person_id) FROM noc_region AS T1 INNER JOIN person_region AS T2 ON T1.id = T2.region_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.region_name = 'Philippines' AND T3.gender = 'F'

Database: soccer_2016
Question: What is the name of the youngest player?
Evidence: name refers to Player_Name; youngest player refers to max(DOB)
SQL: SELECT Player_Name FROM Player ORDER BY DOB DESC LIMIT 1

Database: food_inspection_2
Question: Who is responsible for most of the inspections? Give the full name.
Evidence: full name refers to first_name, last_name; most of the inspections refers to max(count(employee_id))
SQL: SELECT T.first_name, T.last_name FROM ( SELECT T2.employee_id, T2.first_name, T2.last_name, COUNT(T1.inspection_id) FROM inspection AS T1 INNER JOIN employee AS T2 ON T1.employee_id = T2.employee_id GROUP BY T2.employee_id, T2.first_name, T2.last_name ORDER BY COUNT(T1.inspection_id) DESC LIMIT 1 ) AS T

Database: soccer_2016
Question: Which year do the majority of the players were born?
Evidence: year refers to DOB; majority of the players refers to max(count(Player_Id))
SQL: SELECT DOB FROM Player GROUP BY DOB ORDER BY COUNT(DOB) DESC LIMIT 1

Database: retails
Question: What are the shipping methods for the orders on 12/31/1994?
Evidence: shipping method refers to l_shipmode; order on 12/31/1994 refers to o_orderdate = '1994-12-31'
SQL: SELECT DISTINCT T2.l_shipmode FROM orders AS T1 INNER JOIN lineitem AS T2 ON T1.o_orderkey = T2.l_orderkey WHERE T1.o_orderdate = '1994-12-31'

Database: cs_semester
Question: Calculate the difference between the average satisfaction of the students with high salaries and no salary.
Evidence: average satisfaction difference = SUBTRACT(AVG(sat where salary = 'high')), (AVG(sat where salary = 'free')); satisfaction refers to sat; no salary refers to salary = 'free';
SQL: SELECT AVG(T2.sat) - ( SELECT AVG(T2.sat) FROM RA AS T1 INNER JOIN registration AS T2 ON T1.student_id = T2.student_id WHERE T1.salary = 'free' ) AS diff FROM RA AS T1 INNER JOIN registration AS T2 ON T1.student_id = T2.student_id WHERE T1.salary = 'high'

Database: codebase_comments
Question: For the repository which got '8094' Stars, how many solutions does it contain?
Evidence: repository refers to Repo.Id;
SQL: SELECT COUNT(T2.RepoId) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T1.Stars = 8094

Database: talkingdata
Question: On which brand of phone are the most applications installed?
Evidence: brand of phone refers to phone_brand; most applications are installed refers to MAX(COUNT(is_installed = 1));
SQL: SELECT T.phone_brand FROM ( SELECT T1.phone_brand, COUNT(T4.is_active) AS num FROM phone_brand_device_model2 AS T1 INNER JOIN gender_age AS T2 ON T1.device_id = T2.device_id INNER JOIN events_relevant AS T3 ON T2.device_id = T3.device_id INNER JOIN app_events_relevant AS T4 ON T3.event_id = T4.event_id WHERE T4.is_active = 1 GROUP BY T1.phone_brand ) AS T ORDER BY T.num DESC LIMIT 1

Database: donor
Question: What date did the project with he 'Lets Share Ideas essay' went live?
Evidence: date refer to date_posted; Lets Share Ideas refer to title
SQL: SELECT T1.date_posted FROM projects AS T1 INNER JOIN essays AS T2 ON T1.projectid = T2.projectid WHERE T2.title LIKE 'Lets Share Ideas'

Database: address
Question: Show the alias for the county at coordinate (18.090875, -66.867756).
Evidence: coordinates refers to latitude, longitude; latitude = '18.090875; longitude = '-66.867756'
SQL: SELECT T2.alias FROM zip_data AS T1 INNER JOIN alias AS T2 ON T1.zip_code = T2.zip_code WHERE T1.latitude = 18.090875 AND T1.longitude = -66.867756

Database: public_review_platform
Question: Find the Yelp user with the average 5-star rating of all reviews who has been yelping the longest.
Evidence: Yelp user refers to user_id; average 5-star rating refers to user_average_stars = 5; yelping the longest refers to min(user_yelping_since_year)
SQL: SELECT user_id FROM Users WHERE user_average_stars = 5 ORDER BY user_yelping_since_year ASC LIMIT 1

Database: software_company
Question: List the educationnum and response of customers within the age of 20 to 30 that has the highest number of inhabitants among the group.
Evidence: age of 20 to 30 refers to age BETWEEN 20 AND 30; the highest number of inhabitants refers to MAX(INHABITANTS_K);
SQL: SELECT T1.EDUCATIONNUM, T2.RESPONSE FROM Customers AS T1 INNER JOIN Mailings1_2 AS T2 ON T1.ID = T2.REFID INNER JOIN Demog AS T3 ON T1.GEOID = T3.GEOID WHERE T1.age >= 20 AND T1.age <= 30 ORDER BY T3.INHABITANTS_K DESC LIMIT 1

Database: coinmarketcap
Question: What was the price of 1 Bitcoin in 2016?
Evidence: 2016 refers to historical date where year(date) = 2016
SQL: SELECT AVG(T2.price) FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Bitcoin' AND STRFTIME('%Y', T2.date) = '2016'

Database: cars
Question: What is the average price of model 70 cars?
Evidence: model 70 refers to model = 70; average price = avg(price) where model = 70
SQL: SELECT AVG(T2.price) FROM data AS T1 INNER JOIN price AS T2 ON T1.ID = T2.ID WHERE T1.model = 70

Database: retail_world
Question: Calculate the average salary per order for Andrew Fuller.
Evidence: average salary = AVG(Salary)
SQL: SELECT CAST(SUM(T1.Salary) AS REAL) / COUNT(T2.EmployeeID) FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID WHERE T1.FirstName = 'Andrew' AND T1.LastName = 'Fuller'