Database: cookbook
Question: What is the unsaturated fat content in the recipe "Raspberry Chiffon Pie"?
Evidence: Raspberry Chiffon Pie refers to title; unsaturated fat refers to SUBTRACT(total_fat, sat_fat)
SQL: SELECT T2.total_fat - T2.sat_fat FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T1.title = 'Raspberry Chiffon Pie'

Database: retail_complains
Question: What was the issue that the client with the longest server time faced?
Evidence: longest server time refers to MAX(ser_time);
SQL: SELECT T2.Issue FROM callcenterlogs AS T1 INNER JOIN events AS T2 ON T1.`Complaint ID` = T2.`Complaint ID` WHERE T1.ser_time = ( SELECT MAX(ser_time) FROM callcenterlogs )

Database: retails
Question: What proportion of suppliers are from Asia?
Evidence: Asia refers to r_name = 'ASIA'; proportion = divide(count(s_name where r_name = 'ASIA'), count(s_name)) * 100%
SQL: SELECT CAST(SUM(IIF(T1.r_name = 'ASIA', 1, 0)) AS REAL) * 100 / COUNT(T1.r_regionkey) FROM region AS T1 INNER JOIN nation AS T2 ON T1.r_regionkey = T2.n_regionkey INNER JOIN supplier AS T3 ON T2.n_nationkey = T3.s_nationkey

Database: authors
Question: Among all publications containing keywords 'Turbulent Fluids', what percentage of them was published in the journal named 'Physics of Fluids'?
Evidence: 'Physics of Fluids' is the FullName of journal; percentage = DIVIDE(SUM(Keyword = 'Turbulent Fluids'), SUM(FullName = 'Physics of Fluids')) as percentage
SQL: SELECT CAST(SUM(CASE WHEN T1.Keyword = 'Turbulent Fluids' THEN 1 ELSE 0 END) AS REAL) / SUM(CASE WHEN T2.FullName = 'Physics of Fluids' THEN 1 ELSE 0 END) FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id

Database: movies_4
Question: For all the movies which were produced by Cruel and Unusual Films, which one has the most popularity?
Evidence: produced by Cruel and Unusual Films refers to company_name = 'Cruel and Unusual Films'; most popularity refers to max(popularity)
SQL: SELECT T3.title FROM production_company AS T1 INNER JOIN movie_company AS T2 ON T1.company_id = T2.company_id INNER JOIN movie AS T3 ON T2.movie_id = T3.movie_id WHERE T1.company_name = 'Cruel and Unusual Films' ORDER BY T3.popularity DESC LIMIT 1

Database: codebase_comments
Question: How much is the processed time of downloading the most popular repository?
Evidence: more watchers mean that this repository is more popular;
SQL: SELECT ProcessedTime FROM Repo WHERE Watchers = ( SELECT MAX(Watchers) FROM Repo )

Database: beer_factory
Question: For the customer who leaves the review content of "Tastes like Australia.", when was his/her first purchase date?
Evidence: review content of "Tastes like Australia." refers to Review = 'Tastes like Australia.';
SQL: SELECT T1.FirstPurchaseDate FROM customers AS T1 INNER JOIN rootbeerreview AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.Review = 'Tastes like Australia.'

Database: law_episode
Question: How many nominations did Law and Order season 9, episode 20 get?
Evidence: Law and Order refers to series = 'Law and Order'
SQL: SELECT COUNT(T2.award_id) FROM Episode AS T1 INNER JOIN Award AS T2 ON T1.episode_id = T2.episode_id WHERE T2.series = 'Law and Order' AND T1.season = 9 AND T1.episode = 20

Database: european_football_1
Question: Please provide the names of any three away teams that competed in the Italian divisions.
Evidence: Italian means belong to country = 'Italy";
SQL: SELECT T1.AwayTeam FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div=T2.division WHERE T2.country = 'Italy' LIMIT 3

Database: regional_sales
Question: How many sales orders were processed by the store located in Chandler in 2020?
Evidence: "Chandler" is the City Name; orders refers to OrderDate; in 2020 refers to Substr(OrderDate, -2) = '20'
SQL: SELECT SUM(CASE WHEN T2.`City Name` = 'Chandler' AND T1.OrderDate LIKE '%/%/20' THEN 1 ELSE 0 END) FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID

Database: retails
Question: What are the top 2 order keys of the item with the highest amount of extended price?
Evidence: the highest amount of extended price refers to MAX(l_extendedprice);
SQL: SELECT l_orderkey FROM lineitem ORDER BY l_extendedprice DESC LIMIT 2

Database: talkingdata
Question: What percentage of vivo devices belong to users with no information?
Evidence: percentage = MULTIPLY(DIVIDE(SUM(gender = NULL and age = NULL and `group` = NULL), COUNT(device_id)), 1.0); vivo devices refers to phone_brand = 'vivo'; no information refers to gender = NULL AND age = NULL AND `group` = NULL;
SQL: SELECT SUM(IIF(T1.gender IS NULL AND T1.age IS NULL AND T1.`group` IS NULL, 1, 0)) / COUNT(T1.device_id) AS per FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T2.phone_brand = 'vivo'

Database: donor
Question: Among public magnet schools,what percentage of schools that receive the donated resources as books?
Evidence: magnet school refers to school_magnet = 't'; donated resources as books refers to project_resource_type = 'Books'; percentage = Divide(Count(projectid) where project_resource_type = 'Books', Count(projectid))*100
SQL: SELECT CAST(SUM(CASE WHEN T1.project_resource_type = 'Books' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.projectid) FROM resources AS T1 INNER JOIN projects AS T2 ON T1.projectid = T2.projectid WHERE T2.school_magnet = 't'

Database: beer_factory
Question: Among all the root beers purchased by Frank-Paul Santangelo, how many of them were non-sweetened?
Evidence: non-sweetened refers to honey = 'FALSE' AND artificialsweetener = 'FALSE';
SQL: SELECT COUNT(T1.CustomerID) FROM customers AS T1 INNER JOIN `transaction` AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN rootbeer AS T3 ON T2.RootBeerID = T3.RootBeerID INNER JOIN rootbeerbrand AS T4 ON T3.BrandID = T4.BrandID WHERE T1.First = 'Frank-Paul' AND T1.Last = 'Santangelo' AND T4.ArtificialSweetener = 'FALSE' AND T4.Honey = 'FALSE'

Database: olympics
Question: What is the NOC code of the region of the competitors weighted 77 kg?
Evidence: NOC code refers to noc; competitors weighted 77 kg refer to person_id where weight = 77;
SQL: SELECT T1.noc 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 T3.weight = 77

Database: world
Question: What is the capital city of the country with largest population?
Evidence: capital city refers to Capital; largest population refers to MAX(Population);
SQL: SELECT T1.Capital FROM Country AS T1 INNER JOIN City AS T2 ON T1.Code = T2.CountryCode ORDER BY T1.Population DESC LIMIT 1

Database: retail_complains
Question: Between 1/1/2017 and 4/1/2017, what is the average server time of calls under the server DARMON?
Evidence: between 1/1/2017 and 4/1/2017 refers to Date received between '2017-01-01' and '2017-04-01'; average server time refers to avg(ser_time)
SQL: SELECT AVG(CAST(SUBSTR(ser_time, 4, 2) AS REAL)) FROM callcenterlogs WHERE `Date received` BETWEEN '2017-01-01' AND '2017-04-01'

Database: coinmarketcap
Question: Which crytocurrency had a bigger number of coins circulating in the market and in the general public's hands on 2013/4/28, Bitcoin or Litecoin?
Evidence: a bigger number of coins circulating in the market refers to max(circulating_supply); on 2013/4/28 refers to date = '2013-04-28'
SQL: SELECT T1.name FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name IN ('Bitcoin', 'Litecoin') ORDER BY T2.circulating_supply DESC LIMIT 1

Database: address
Question: In which county can you find the city with the highest number of females?
Evidence: highest number of females refers to Max(female_population)
SQL: SELECT T4.county FROM zip_data AS T3 INNER JOIN country AS T4 ON T3.zip_code = T4.zip_code GROUP BY T4.county ORDER BY T3.female_population DESC LIMIT 1