Database: shipping
Question: Which headquarter's truck has the highest shipments in year 2016?
Evidence: in 2016 refers to CAST(ship_date AS DATE) = 2016; make = 'Peterbilt' means headquarter is 'Texas (TX)', make = 'Mack' means headquarter is 'North Carolina (NC)', make = 'Kenworth' means headquarter is 'Washington (WA)'; highest shipment refers to MAX(COUNT(ship_id))
SQL: SELECT CASE WHEN T2.make = 'Peterbilt' THEN 'Texas (TX)' WHEN T2.make = 'Mack' THEN 'North Carolina (NC)' WHEN T2.make = 'Kenworth' THEN 'Washington (WA)' END AS "result" FROM shipment AS T1 INNER JOIN truck AS T2 ON T1.truck_id = T2.truck_id WHERE CAST(T1.ship_date AS DATE) = 2016 GROUP BY T2.make ORDER BY COUNT(T1.ship_id) DESC LIMIT 1

Database: bike_share_1
Question: In 2014, what is the shortest duration of trips by subscribers which started at 2nd at Folsom and ended in the 5th at Howard stations, and by how much shorter than the average? Give me the minimum temperature, maximum gust speed and weather event on that trip.
Evidence: the shortest duration refers to MIN(duration); subscription_type = 'Subscriber', start_station_name = '2nd at Folsom', end_station_name = '5th at Howard'; in 2014 refers to time period between start_date = '1/1/2014 0:00' and end_date = '12/31/2014 11:59; Average duration = DIVIDE(SUM(duration), COUNT(id));
SQL: SELECT MIN(T1.duration) , MIN(T1.duration) - AVG(T1.duration), T2.min_temperature_f FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T1.start_date = '1/1/2014 0:00' AND T1.end_date = '12/31/2014 11:59' AND T1.start_station_name = '2nd at Folsom' AND T1.end_station_name = '5th at Howard' AND T1.subscription_type = 'Subscriber'

Database: restaurant
Question: List all the cities in Sonoma County.
SQL: SELECT city FROM geographic WHERE county = 'sonoma county'

Database: shooting
Question: From the 'Injured' statuses of the subject, what is the ratio of weapons used are knife against handgun?
Evidence: the 'Shoot and Miss' status refers to subject_statuses = 'Shoot and Miss'; knife refers to subject_weapon = 'knife'; toy handgun refers to subject_weapon = 'toy handgun'; ratio = divide(count(case_number where subject_weapon = 'knife'), count(case_number where subject_weapon = 'toy handgun')) where subject_statuses = 'Shoot and Miss'
SQL: SELECT CAST(SUM(T1.subject_weapon = 'Knife') AS REAL) * 100 / SUM(T1.subject_weapon = 'Handgun') FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T1.subject_statuses = 'Injured'

Database: talkingdata
Question: How many women have apps from the game-Finding fault category installed on their device?
Evidence: women refers to gender = 'F'; installed refers to is_installed = 1;
SQL: SELECT COUNT(T1.device_id) FROM gender_age AS T1 INNER JOIN events_relevant AS T2 ON T1.device_id = T2.device_id INNER JOIN app_events_relevant AS T3 ON T2.event_id = T3.event_id WHERE T1.age < 23 AND T1.gender = 'F' AND T3.is_active = 0 AND T3.is_installed = 1

Database: retail_world
Question: List down the customer ids who placed order with Michael Suyama.
SQL: SELECT T2.CustomerID FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID WHERE T1.FirstName = 'Michael' AND T1.LastName = 'Suyama'

Database: synthea
Question: What is the percentage of the most common conditions for patients age 60 and above?
Evidence: most common condition refers to MAX(COUNT(conditions.DESCRIPTION)); age 60 and above refers to SUBTRACT(conditions.START, birthdate) > 60; percentage = MULTIPLY(DIVIDE(SUM(patients.patient WHERE MAX(COUNT(conditions.DESCRIPTION)) AND SUBTRACT(conditions.START, birthdate) > 60))), COUNT(patients.patient WHERE MAX(COUNT(conditions.DESCRIPTION)), 10);
SQL: SELECT CAST(SUM(CASE WHEN T5.DESCRIPTION = T3.DESCRIPTION THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T3.patient) FROM ( SELECT T2.DESCRIPTION, T1.patient FROM patients AS T1 INNER JOIN conditions AS T2 ON T1.patient = T2.PATIENT WHERE ROUND((strftime('%J', T2.START) - strftime('%J', T1.birthdate)) / 365) > 60 GROUP BY T2.DESCRIPTION ORDER BY COUNT(T2.DESCRIPTION) DESC LIMIT 1 ) AS T3 INNER JOIN patients AS T4 ON T3.patient = T4.patient INNER JOIN conditions AS T5 ON T4.patient = T5.PATIENT WHERE ROUND((strftime('%J', T5.START) - strftime('%J', T4.birthdate)) / 365) > 60

Database: computer_student
Question: List any five of course IDs with professor IDs who taught master courses.
Evidence: professor IDs refers to taughtBy.p_id; master course refers to courseLevel = 'Level_500'
SQL: SELECT T1.course_id, T2.p_id FROM course AS T1 INNER JOIN taughtBy AS T2 ON T1.course_id = T2.course_id WHERE T1.courseLevel = 'Level_500' LIMIT 5

Database: world_development_indicators
Question: List out the series code and country code of the poor countries that located in Latin American & Carribbean.
Evidence: Latin American & Carribbean is the name of the region; poor countries refers to IncomeGroup = 'Low income';
SQL: SELECT T2.SeriesCode, T2.CountryCode FROM Country AS T1 INNER JOIN CountryNotes AS T2 ON T1.CountryCode = T2.Countrycode WHERE T1.Region = 'Latin America & Caribbean' AND t1.incomegroup = 'Low income'

Database: donor
Question: For the donation of the project 'Awesome Audiobooks Make Avid Readers', what was the percentage of the tip in the total amount?
Evidence: Awesome Audiobooks Make Avid Readers' is the title; percentage = Divie(donation_optional_support, donation_total)*100;
SQL: SELECT CAST(SUM(T2.donation_optional_support) AS REAL) * 100 / SUM(T2.donation_total) FROM essays AS T1 INNER JOIN donations AS T2 ON T1.projectid = T2.projectid WHERE T1.title LIKE 'Awesome Audiobooks Make Avid Readers'

Database: codebase_comments
Question: What is the time of sampling of the solution with the highest sampling time? Indicate the id number of the solution.
Evidence: highest sampling time refers to max(SampledAt); id number of the solution refers to SolutionId;
SQL: SELECT DISTINCT SampledAt, SolutionId FROM Method WHERE SampledAt = ( SELECT MAX(SampledAt) FROM Method )

Database: movie_3
Question: Calculate the percentage of total payment of MARGARET MOORE customers.
Evidence: percentage = divide(sum(amount where first_name = 'MARGARET' and last_name = 'MOORE'), sum(amount)) * 100%
SQL: SELECT CAST(SUM(IIF(T2.first_name = 'MARGARET' AND T2.last_name = 'MOORE', T1.amount, 0)) AS REAL) * 100 / SUM(T1.amount) FROM payment AS T1 INNER JOIN customer AS T2 ON T1.customer_id = T2.customer_id

Database: movie_3
Question: How many actors with the surname Kilmer are there?
Evidence: surname means last_name;
SQL: SELECT COUNT(actor_id) FROM actor WHERE last_name = 'Kilmer'

Database: sales_in_weather
Question: For the weather station which recorded the highest temperature above the 30-year normal, how many stores does it have?
Evidence: highest temperature above the 30-year normal refers to Max(depart)
SQL: SELECT store_nbr FROM relation WHERE station_nbr = ( SELECT station_nbr FROM weather ORDER BY depart DESC LIMIT 1 )

Database: superstore
Question: How many office supply orders were made by Cindy Stewart in the south superstore?
Evidence: office supply refers to Category = 'Office Supplies'
SQL: SELECT COUNT(*) FROM south_superstore AS T1 INNER JOIN people AS T2 ON T1.`Customer ID` = T2.`Customer ID` INNER JOIN product AS T3 ON T3.`Product ID` = T1.`Product ID` WHERE T3.Category = 'Office Supplies' AND T2.`Customer Name` = 'Cindy Stewart'

Database: language_corpus
Question: What is the average occurrence of the word "grec" on each Wikipedia page that has this word?
Evidence: grec refers to word = 'grec'; AVG(occurrences where word = 'grec')
SQL: SELECT CAST(SUM(T2.occurrences) AS REAL) / COUNT(T1.wid) FROM words AS T1 INNER JOIN pages_words AS T2 ON T1.wid = T2.wid WHERE T1.word = 'grec'