Database: retail_world
Question: List the employees' full names and ages in 2022 who lived in London.
Evidence: in London refers to City = 'London'; ages in 2022 refers to SUBTRACT(2022, year(BirthDate)); full names refers to FirstName, LastName
SQL: SELECT TitleOfCourtesy, FirstName, LastName , TIMESTAMPDIFF(YEAR, BirthDate, NOW()) AS ages FROM Employees WHERE City = 'London'

Database: language_corpus
Question: How many times the word "desena" occurs?
Evidence: How many times occurs refers to occurrences;
SQL: SELECT occurrences FROM words WHERE word = 'desena'

Database: talkingdata
Question: What is the percentage of users who experienced event number 6 who have the app installed but do not use the app?
Evidence: percentage = MULTIPLY(DIVIDE(SUM(is_installed = 1 and is_active = 0), COUNT(app_id)), 1.0); event number refers to event_id = 6; installed refers to is_installed = 1; do not use refers to is_active = 0;
SQL: SELECT SUM(IIF(is_installed = 1 AND is_active = 0, 1, 0)) / COUNT(app_id) AS perrcent FROM app_events WHERE event_id = 6

Database: talkingdata
Question: Among the HTC users, calculate the percentage of female users who are over 80.
Evidence: HTC refers to phone_brand = 'HTC'; percentage = DIVIDE(SUM(gender = 'F' AND age > 80), COUNT(device_id)); female refers to gender = 'F'; over 80 refers to age > 80
SQL: SELECT SUM(IIF(T1.gender = 'F' AND T1.age > 80, 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 = 'HTC'

Database: soccer_2016
Question: What is the difference between the number of matches where SC Ganguly played as a Captain and those matches where he played other roles?
Evidence: SC Ganguly refers to Player_Name = 'SC Ganguly'; played as a Captain refers to Role_Id = 1; played other roles refers to Role_Id > 1; difference refers to SUBTRACT(COUNT(Role_Id = 1), COUNT(Role_Id > 1))
SQL: SELECT SUM(CASE WHEN T3.Role_Id = 1 THEN 1 ELSE 0 END) - SUM(CASE WHEN T3.Role_Id > 1 THEN 1 ELSE 0 END) FROM Player_Match AS T1 INNER JOIN Player AS T2 ON T1.Player_Id = T2.Player_Id INNER JOIN Rolee AS T3 ON T1.Role_Id = T3.Role_Id WHERE T2.Player_Name = 'SC Ganguly'

Database: language_corpus
Question: What is the percentage of words in the Catalan language that have a repetition of more than 16,000 times?
Evidence: words in the Catalan language refers lid = 1; repetition of more than 16,000 times refers to occurrences > 16000; DIVIDE(COUNT(words where lid = 1 and occurrences > 16000), COUNT(words where lid = 1)) as percentage;
SQL: SELECT CAST(COUNT(CASE WHEN occurrences > 16000 THEN lid ELSE NULL END) AS REAL) * 100 / COUNT(lid) FROM langs_words

Database: world_development_indicators
Question: Which indicator name uses the Weighted average method and has the lowest value?
Evidence: AggregationMethod = 'Weighted average'; the lowest value implies MIN(Value);
SQL: SELECT T1.IndicatorName, MIN(T1.Value) FROM Indicators AS T1 INNER JOIN Series AS T2 ON T1.IndicatorName = T2.IndicatorName WHERE T2.AggregationMethod = 'Weighted average'

Database: retail_world
Question: Please list the names of all the products ordered in order no. 10248.
Evidence: names of the products refers to ProductName; order no. refers to OrderID;
SQL: SELECT T1.ProductName FROM Products AS T1 INNER JOIN `Order Details` AS T2 ON T1.ProductID = T2.ProductID WHERE T2.OrderID = 10248

Database: books
Question: List the author's and publisher's name of the book published on July 10, 1997.
Evidence: author's name refers to author_name; publisher's name refers to publisher_name; book published on July 10, 1997 refers to publication_date LIKE '1997-07-10'
SQL: SELECT T3.author_name, T4.publisher_name FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id INNER JOIN publisher AS T4 ON T4.publisher_id = T1.publisher_id WHERE T1.publication_date = '1997-07-10'

Database: synthea
Question: How long did Elly Koss have to take Acetaminophen 160 MG?
Evidence: how long = SUBTRACT(julianday(medications.stop), julianday(medications.START)); Acetaminophen 160 MG refers to medications.DESCRIPTION = 'Acetaminophen 160 MG';
SQL: SELECT strftime('%J', T2.STOP) - strftime('%J', T2.START) AS days FROM patients AS T1 INNER JOIN medications AS T2 ON T1.patient = T2.PATIENT WHERE T1.first = 'Elly' AND last = 'Koss' AND T2.DESCRIPTION = 'Acetaminophen 160 MG'

Database: soccer_2016
Question: Who is the player who won the first ever "man of the match" award?
Evidence: name of the player refers to Player_Name; the first ever refers to min(match_date); "man of the match" award refers to Player_Id in 'Man_of_the_Match'
SQL: SELECT Player_Name FROM Player WHERE Player_Id = ( SELECT Man_of_the_Match FROM `Match` ORDER BY match_date ASC LIMIT 1 )

Database: retails
Question: How many orders of more than 10 items have been returned?
Evidence: more than 10 items have been returned refer to l_returnflag = 'R' where l_quantity > 10; orders refer to l_orderkey;
SQL: SELECT COUNT(l_linenumber) FROM lineitem WHERE l_quantity > 10 AND l_returnflag = 'R'

Database: student_loan
Question: State name of disabled students who have the longest duration of absense from school.
Evidence: longest duration of absense refers to MAX(month)
SQL: SELECT T1.name FROM disabled AS T1 INNER JOIN longest_absense_from_school AS T2 ON T1.name = T2.name ORDER BY T2.month DESC LIMIT 1

Database: codebase_comments
Question: What is the task of the method whose tokenized name is "online median filter test median window filling"?
Evidence: tokenized name refers to NameTokenized; task of the method refers to the second part of name deliminated by "."; for example, the task of 'LinqToDelicious.HttpWebRequestFactory.Create' is 'HttpWebRequestFactory'
SQL: SELECT SUBSTR(SUBSTR(Name, INSTR(Name, '.') + 1), 1, INSTR(SUBSTR(Name, INSTR(Name, '.') + 1), '.') - 1) task FROM Method WHERE NameTokenized = 'online median filter test median window filling'

Database: chicago_crime
Question: What types of domestic crimes have occurred the most in the North Lawndale community?
Evidence: "North Lawndale' is the community_area_name; occur the most domestic crime refers to Max(Count(domestic = 'TRUE'))
SQL: SELECT T2.domestic FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T2.community_area_no = T1.community_area_no WHERE T1.community_area_name = 'North Lawndale' AND T2.domestic = 'TRUE' GROUP BY T2.domestic ORDER BY COUNT(T2.domestic) DESC LIMIT 1

Database: european_football_1
Question: Who is the winner of the game happened on 2009/10/10, between "East Fife" and "Dumbarton"?
Evidence: 2009/10/10 is a date; the winner refers to FTR = 'A'; East Fife and Dumbarton are name of teams where HomeTeam = 'East Fife'; AwayTeam = 'Dumbarton';
SQL: SELECT CASE WHEN FTR = 'H' THEN 'East Fife' ELSE 'Dumbarton' END WINNER FROM matchs WHERE Date = '2009-10-10' AND HomeTeam = 'East Fife' AND AwayTeam = 'Dumbarton'

Database: image_and_language
Question: List all the ids of the images that have a self-relation relationship.
Evidence: ids of the images refers to IMG_ID; self-relations refers to OBJ1_SAMPLE_ID = OBJ2_SAMPLE_ID
SQL: SELECT DISTINCT IMG_ID FROM IMG_REL WHERE OBJ1_SAMPLE_ID = OBJ2_SAMPLE_ID

Database: chicago_crime
Question: In the least populated community, what is the most common location of all the reported crime incidents?
Evidence: least populated refers to Min(Population); community refers to community_area_no; most common location refers to Max(Count(location_description))
SQL: SELECT T2.location_description FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T1.community_area_no = T2.community_area_no WHERE T1.population = ( SELECT MIN(population) FROM Community_Area ) AND T2.location_description IS NOT NULL GROUP BY T2.location_description