RuihanCao/bird-dev-corrected
03
1[2 {3 "question_id": 37,4 "db_id": "california_schools",5 "question": "What is the complete address of the school with the lowest excellence rate? Indicate the Street, City, Zip and State.",6 "evidence": "Execellence Rate = NumGE1500 / NumTstTakr; complete address has Street, City, Zip, State",7 "SQL": "SELECT T2.Street, T2.City, T2.Zip, T2.State FROM schools AS T2 INNER JOIN (SELECT cds, CAST(NumGE1500 AS REAL) / NumTstTakr AS rate FROM satscores WHERE NumGE1500 IS NOT NULL AND NumTstTakr IS NOT NULL AND NumTstTakr != 0) AS T1 ON T2.CDSCode = T1.cds WHERE T1.rate = (SELECT MIN(CAST(NumGE1500 AS REAL) / NumTstTakr) AS min_rate FROM satscores WHERE NumGE1500 IS NOT NULL AND NumTstTakr IS NOT NULL AND NumTstTakr != 0) ORDER BY T2.CDSCode",8 "difficulty": "moderate",9 "index": 010 },11 {12 "question_id": 68,13 "db_id": "california_schools",14 "question": "Which county reported the most number of school closure in the 1980s with school wonership code belonging to Youth Authority Facilities (CEA)?",15 "evidence": "Youth Authority Facilities (CEA) refers to SOC = 11; 1980s = years between 1980 and 1989",16 "SQL": "SELECT County FROM (SELECT County, COUNT(School) AS SchoolCount FROM schools WHERE strftime('%Y', ClosedDate) BETWEEN '1980' AND '1989' AND StatusType = 'Closed' AND SOC = 11 GROUP BY County) WHERE SchoolCount = (SELECT MAX(SchoolCount) FROM (SELECT COUNT(School) AS SchoolCount FROM schools WHERE strftime('%Y', ClosedDate) BETWEEN '1980' AND '1989' AND StatusType = 'Closed' AND SOC = 11 GROUP BY County))",17 "difficulty": "moderate",18 "index": 119 },20 {21 "question_id": 80,22 "db_id": "california_schools",23 "question": "What is the school type of the school with the highest latitude? Indicate the name of the school as well as the latitude coordinates.",24 "evidence": "",25 "SQL": "SELECT T1.`School Type`, T1.`School Name`, T2.Latitude FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.Latitude = (SELECT MAX(Latitude) FROM schools)",26 "difficulty": "simple",27 "index": 228 },29 {30 "question_id": 81,31 "db_id": "california_schools",32 "question": "In which city can you find the school in the state of California with the lowest latitude coordinates and what is its lowest grade? Indicate the school name.",33 "evidence": "State of California refers to state = 'CA'",34 "SQL": "SELECT T2.City, T1.`Low Grade`, T1.`School Name` FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.State = 'CA' AND T2.Latitude IS NOT NULL AND T2.Latitude = (SELECT MIN(Latitude) FROM schools WHERE State = 'CA' AND Latitude IS NOT NULL)",35 "difficulty": "moderate",36 "index": 337 },38 {39 "question_id": 82,40 "db_id": "california_schools",41 "question": "What is the grade span offered in the school with the highest longitude?",42 "evidence": "",43 "SQL": "SELECT GSoffered FROM schools WHERE ABS(longitude) = (SELECT MAX(ABS(longitude)) FROM schools)",44 "difficulty": "simple",45 "index": 446 },47 {48 "question_id": 101,49 "db_id": "financial",50 "question": "List out the accounts who have the earliest trading date in 1995 ?",51 "evidence": "",52 "SQL": "SELECT account_id FROM trans WHERE STRFTIME('%Y', date) = '1995' ORDER BY date ASC LIMIT 1",53 "difficulty": "simple",54 "index": 555 },56 {57 "question_id": 189,58 "db_id": "financial",59 "question": "Name the account numbers of female clients who are oldest and have lowest average salary?",60 "evidence": "Female refers to 'F' in the gender; A11 contains information about average salary",61 "SQL": "SELECT T3.account_id FROM client AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id INNER JOIN account AS T3 ON T2.district_id = T3.district_id WHERE T1.gender = 'F' AND T1.birth_date = (SELECT MIN(birth_date) FROM client WHERE gender = 'F') AND T2.A11 = (SELECT MIN(T2.A11) FROM client AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id WHERE T1.gender = 'F' AND T1.birth_date = (SELECT MIN(birth_date) FROM client WHERE gender = 'F'))",62 "difficulty": "moderate",63 "index": 664 },65 {66 "question_id": 244,67 "db_id": "toxicology",68 "question": "Is the molecule with the most double bonds carcinogenic?",69 "evidence": "double bond refers to bond_type = ' = '; label = '+' mean molecules are carcinogenic",70 "SQL": "SELECT T1.label FROM molecule AS T1 INNER JOIN (SELECT T.molecule_id, COUNT(T.bond_type) AS bond_count FROM bond AS T WHERE T.bond_type = '=' GROUP BY T.molecule_id HAVING bond_count = (SELECT MAX(bond_count) FROM (SELECT molecule_id, COUNT(bond_type) AS bond_count FROM bond WHERE bond_type = '=' GROUP BY molecule_id) AS subquery) ORDER BY bond_count DESC) AS T2 ON T1.molecule_id = T2.molecule_id",71 "difficulty": "moderate",72 "index": 773 },74 {75 "question_id": 250,76 "db_id": "toxicology",77 "question": "Of all the carcinogenic molecules, which one has the most double bonds?",78 "evidence": "label = '+' mean molecules are carcinogenic; double bond refers to bond_type = ' = ';",79 "SQL": "SELECT T.molecule_id FROM (SELECT T3.molecule_id, COUNT(T1.bond_type) AS bond_count FROM bond AS T1 INNER JOIN molecule AS T3 ON T1.molecule_id = T3.molecule_id WHERE T3.label = '+' AND T1.bond_type = '=' GROUP BY T3.molecule_id HAVING bond_count = (SELECT MAX(bond_count) FROM (SELECT T3.molecule_id, COUNT(T1.bond_type) AS bond_count FROM bond AS T1 INNER JOIN molecule AS T3 ON T1.molecule_id = T3.molecule_id WHERE T3.label = '+' AND T1.bond_type = '=' GROUP BY T3.molecule_id) AS subquery) ORDER BY bond_count DESC) AS T",80 "difficulty": "moderate",81 "index": 882 },83 {84 "question_id": 342,85 "db_id": "card_games",86 "question": "List the card names with value that cost more converted mana for the face.",87 "evidence": "more converted mana for the face refers to Max(faceConvertedManaCost);",88 "SQL": "SELECT name FROM cards WHERE faceConvertedManaCost IS NOT NULL AND faceConvertedManaCost = (SELECT MAX(faceConvertedManaCost) FROM cards WHERE faceConvertedManaCost IS NOT NULL)",89 "difficulty": "simple",90 "index": 991 },92 {93 "question_id": 432,94 "db_id": "card_games",95 "question": "Which Russian set of cards contains the most cards overall?",96 "evidence": "Russian refers to language = 'Russian'; contains the most cards overall refers to MAX(baseSetSize)",97 "SQL": "SELECT T1.id FROM sets AS T1 INNER JOIN set_translations AS T2 ON T1.code = T2.setCode WHERE T2.language = 'Russian' GROUP BY T1.baseSetSize HAVING COUNT(T1.id) = (SELECT MAX(card_count) FROM (SELECT COUNT(T1.id) AS card_count FROM sets AS T1 INNER JOIN set_translations AS T2 ON T1.code = T2.setCode WHERE T2.language = 'Russian' GROUP BY T1.baseSetSize) AS subquery)",98 "difficulty": "moderate",99 "index": 10100 },101 {102 "question_id": 476,103 "db_id": "card_games",104 "question": "Please list the name of the cards in the set Coldsnap with the highest converted mana cost.",105 "evidence": "card set Coldsnap refers to name = 'Coldsnap'",106 "SQL": "SELECT T1.name FROM cards AS T1 INNER JOIN sets AS T2 ON T2.code = T1.setCode WHERE T2.name = 'Coldsnap' AND T1.convertedManaCost = (SELECT MAX(convertedManaCost) FROM cards AS T1 INNER JOIN sets AS T2 ON T2.code = T1.setCode WHERE T2.name = 'Coldsnap')",107 "difficulty": "simple",108 "index": 11109 },110 {111 "question_id": 484,112 "db_id": "card_games",113 "question": "Please list the Italian names of the cards in the set Coldsnap with the highest converted mana cost.",114 "evidence": "card set Coldsnap refers to name = 'Coldsnap'; Italian refers to language = 'Italian'",115 "SQL": "SELECT T2.name FROM foreign_data AS T1 INNER JOIN cards AS T2 ON T2.uuid = T1.uuid INNER JOIN sets AS T3 ON T3.code = T2.setCode WHERE T3.name = 'Coldsnap' AND T1.language = 'Italian' AND T2.convertedManaCost = (SELECT MAX(convertedManaCost) FROM foreign_data AS T1 INNER JOIN cards AS T2 ON T2.uuid = T1.uuid INNER JOIN sets AS T3 ON T3.code = T2.setCode WHERE T3.name = 'Coldsnap' AND T1.language = 'Italian')",116 "difficulty": "moderate",117 "index": 12118 },119 {120 "question_id": 515,121 "db_id": "card_games",122 "question": "When was the oldest mythic card released and what are its legal play formats?",123 "evidence": "the oldest card refers to MIN(originalReleaseDate); mythic card refers to rarity = 'mythic'; legal play refers to status = 'legal'; play format refers to format",124 "SQL": "SELECT T1.originalReleaseDate, T2.format FROM cards AS T1 INNER JOIN legalities AS T2 ON T1.uuid = T2.uuid WHERE T1.rarity = 'mythic' AND T1.originalReleaseDate IS NOT NULL AND T2.status = 'Legal' AND T1.originalReleaseDate = (SELECT MIN(originalReleaseDate) FROM cards AS T1 INNER JOIN legalities AS T2 ON T1.uuid = T2.uuid WHERE T1.rarity = 'mythic' AND T1.originalReleaseDate IS NOT NULL AND T2.status = 'Legal')",125 "difficulty": "moderate",126 "index": 13127 },128 {129 "question_id": 610,130 "db_id": "codebase_community",131 "question": "What are the names of badges that users who have the highest reputation obtained?",132 "evidence": "highest reputation refers to Max(Reputation); user refers to UserId",133 "SQL": "SELECT T2.name FROM users AS T1 INNER JOIN badges AS T2 ON T1.Id = T2.UserId WHERE T1.Reputation = (SELECT MAX(Reputation) FROM users)",134 "difficulty": "simple",135 "index": 14136 },137 {138 "question_id": 621,139 "db_id": "codebase_community",140 "question": "What are the name of badges that users who have the lowest reputation obtained?",141 "evidence": "lowest reputation refers to Min(Reputation); user refers to UserId",142 "SQL": "SELECT T2.Name, T1.Reputation FROM users AS T1 INNER JOIN badges AS T2 ON T1.Id = T2.UserId WHERE T1.Reputation = (SELECT MIN(Reputation) FROM users)",143 "difficulty": "simple",144 "index": 15145 },146 {147 "question_id": 694,148 "db_id": "codebase_community",149 "question": "Provide the text of the latest comment to the post with the title 'Analysing wind data with R' and the display name of the user who left it.",150 "evidence": "the latest comment refers to MAX(CreationDate);",151 "SQL": "SELECT T3.Text, T1.DisplayName FROM users AS T1 INNER JOIN posts AS T2 ON T1.Id = T2.OwnerUserId INNER JOIN comments AS T3 ON T2.Id = T3.PostId WHERE T2.Title = 'Analysing wind data with R' AND T3.CreationDate = (SELECT MAX(T4.CreationDate) FROM users AS T5 INNER JOIN posts AS T6 ON T5.Id = T6.OwnerUserId INNER JOIN comments AS T4 ON T6.Id = T4.PostId WHERE T6.Title = 'Analysing wind data with R')",152 "difficulty": "moderate",153 "index": 16154 },155 {156 "question_id": 736,157 "db_id": "superhero",158 "question": "Who is the dumbest superhero?",159 "evidence": "the dumbest superhero refers to MIN(attribute_value) where attribute_name = 'Intelligence'",160 "SQL": "SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id INNER JOIN attribute AS T3 ON T2.attribute_id = T3.id WHERE T3.attribute_name = 'Intelligence' AND T2.attribute_value = (SELECT MIN(T4.attribute_value) FROM hero_attribute AS T4 INNER JOIN attribute AS T5 ON T4.attribute_id = T5.id WHERE T5.attribute_name = 'Intelligence')",161 "difficulty": "moderate",162 "index": 17163 },164 {165 "question_id": 766,166 "db_id": "superhero",167 "question": "What is the hero's full name with the highest attribute in strength?",168 "evidence": "highest attribute in strength refers to MAX(attribute_value) WHERE attribute_name = 'strength';",169 "SQL": "SELECT T1.full_name FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id INNER JOIN attribute AS T3 ON T2.attribute_id = T3.id WHERE T3.attribute_name = 'Strength' AND T2.attribute_value = (SELECT MAX(T4.attribute_value) FROM hero_attribute AS T4 INNER JOIN attribute AS T5 ON T4.attribute_id = T5.id WHERE T5.attribute_name = 'Strength')",170 "difficulty": "moderate",171 "index": 18172 },173 {174 "question_id": 769,175 "db_id": "superhero",176 "question": "Which superhero has the most durability published by Dark Horse Comics?",177 "evidence": "which superhero refers to superhero_name; most durability refers to MAX(attribute_value) WHERE attribute_name = 'durability'; published by Dark Horse Comics refers to publisher_name = 'Dark Horse Comics';",178 "SQL": "SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id INNER JOIN attribute AS T3 ON T3.id = T2.attribute_id INNER JOIN publisher AS T4 ON T4.id = T1.publisher_id WHERE T4.publisher_name = 'Dark Horse Comics' AND T2.attribute_value = (SELECT MAX(T5.attribute_value) FROM superhero AS T6 INNER JOIN hero_attribute AS T5 ON T6.id = T5.hero_id INNER JOIN publisher AS T7 ON T7.id = T6.publisher_id WHERE T7.publisher_name = 'Dark Horse Comics')",179 "difficulty": "challenging",180 "index": 19181 },182 {183 "question_id": 802,184 "db_id": "superhero",185 "question": "Who is the tallest superhero?",186 "evidence": "who refers to superhero_name; tallest superhero refers to MAX(height_cm);",187 "SQL": "SELECT superhero_name FROM superhero WHERE height_cm = (SELECT MAX(height_cm) FROM superhero)",188 "difficulty": "simple",189 "index": 20190 },191 {192 "question_id": 810,193 "db_id": "superhero",194 "question": "What is the race of the superhero with maximum attribute value?",195 "evidence": "maximum attribute value refers to MAX(attribute_value);",196 "SQL": "SELECT T3.race FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id INNER JOIN race AS T3 ON T1.race_id = T3.id WHERE T2.attribute_value = (SELECT MAX(T4.attribute_value) FROM hero_attribute AS T4)",197 "difficulty": "simple",198 "index": 21199 },200 {201 "question_id": 828,202 "db_id": "superhero",203 "question": "Count the fastest superheroes.",204 "evidence": "fastest refers to attribute_value = 100 WHERE attribute_name = 'Speed';",205 "SQL": "SELECT COUNT(T3.superhero_name) FROM hero_attribute AS T1 INNER JOIN attribute AS T2 ON T1.attribute_id = T2.id INNER JOIN superhero AS T3 ON T1.hero_id = T3.id WHERE T2.attribute_name = 'Speed' AND T1.attribute_value = (SELECT MAX(T4.attribute_value) FROM hero_attribute AS T4 INNER JOIN attribute AS T5 ON T4.attribute_id = T5.id WHERE T5.attribute_name = 'Speed')",206 "difficulty": "simple",207 "index": 22208 },209 {210 "question_id": 832,211 "db_id": "superhero",212 "question": "Name the tallest superhero.",213 "evidence": "tallest superhero refers to MAX(height_cm);",214 "SQL": "SELECT superhero_name FROM superhero WHERE height_cm = (SELECT MAX(height_cm) FROM superhero)",215 "difficulty": "simple",216 "index": 23217 },218 {219 "question_id": 847,220 "db_id": "formula_1",221 "question": "What is the surname of the driver with the best lap time in race number 19 in the second period?",222 "evidence": "race number refers to raceId; second qualifying period refers to q2; best lap time refers to MIN(q2);",223 "SQL": "SELECT T2.surname FROM qualifying AS T1 INNER JOIN drivers AS T2 ON T2.driverId = T1.driverId WHERE T1.raceId = 19 AND T1.q2 IS NOT NULL AND T1.q2 = (SELECT MIN(q2) AS min_q2 FROM qualifying WHERE raceId = 19 AND q2 IS NOT NULL)",224 "difficulty": "simple",225 "index": 24226 },227 {228 "question_id": 906,229 "db_id": "formula_1",230 "question": "Which was Lewis Hamilton first race? What was his points recorded for his first race event?",231 "evidence": "first race refers to min(Year)",232 "SQL": "SELECT T1.name, T2.points FROM races AS T1 INNER JOIN driverStandings AS T2 ON T2.raceId = T1.raceId INNER JOIN drivers AS T3 ON T3.driverId = T2.driverId WHERE T3.forename = 'Lewis' AND T3.surname = 'Hamilton' AND T1.year = (SELECT MIN(T4.year) FROM races AS T4 INNER JOIN driverStandings AS T5 ON T5.raceId = T4.raceId INNER JOIN drivers AS T6 ON T6.driverId = T5.driverId WHERE T6.forename = 'Lewis' AND T6.surname = 'Hamilton')",233 "difficulty": "moderate",234 "index": 25235 },236 {237 "question_id": 908,238 "db_id": "formula_1",239 "question": "What is the most laps f1 races had? Name the race, year and circuit location where the races with most laps was hosted.",240 "evidence": "",241 "SQL": "SELECT T3.lap, T2.name, T2.year, T1.location FROM circuits AS T1 INNER JOIN races AS T2 ON T1.circuitId = T2.circuitId INNER JOIN lapTimes AS T3 ON T3.raceId = T2.raceId WHERE T3.lap = (SELECT MAX(T4.lap) FROM lapTimes AS T4)",242 "difficulty": "simple",243 "index": 26244 },245 {246 "question_id": 1020,247 "db_id": "european_football_2",248 "question": "Which player has the highest overall rating? Indicate the player's api id.",249 "evidence": "highest overall rating refers to MAX(overall_rating);",250 "SQL": "SELECT player_api_id FROM Player_Attributes WHERE overall_rating = (SELECT MAX(overall_rating) FROM Player_Attributes)",251 "difficulty": "simple",252 "index": 27253 },254 {255 "question_id": 1022,256 "db_id": "european_football_2",257 "question": "What is the preferred foot when attacking of the player with the lowest potential?",258 "evidence": "preferred foot when attacking refers to preferred_foot; lowest potential refers to MIN(potential);",259 "SQL": "SELECT preferred_foot FROM Player_Attributes WHERE penalties AND potential = (SELECT MIN(potential) FROM Player_Attributes)",260 "difficulty": "simple",261 "index": 28262 },263 {264 "question_id": 1026,265 "db_id": "european_football_2",266 "question": "Which home team had lost the fewest matches in the 2016 season?",267 "evidence": "home team lost the matches refers to SUBTRACT(home_team_goal, away_team_goal) < 0; 2016 season refers to season = '2015/2016';",268 "SQL": "SELECT t3.team_long_name FROM Match AS t1 INNER JOIN Team AS t3 ON t1.home_team_api_id = t3.team_api_id WHERE t1.season = '2015/2016' AND t1.home_team_goal - t1.away_team_goal < 0 GROUP BY t1.home_team_api_id HAVING COUNT(*) = (SELECT MIN(lost_matches_count) FROM (SELECT home_team_api_id, COUNT(*) AS lost_matches_count FROM Match WHERE season = '2015/2016' AND home_team_goal - away_team_goal < 0 GROUP BY home_team_api_id))",269 "difficulty": "moderate",270 "index": 29271 },272 {273 "question_id": 1028,274 "db_id": "european_football_2",275 "question": "In Scotland Premier League, which away team won the most during the 2010 season?",276 "evidence": "Scotland Premier League refers to League.name = 'Scotland Premier League'; away team refers to away_team_api_id; away team that won the most refers to MAX(SUBTRACT(away_team_goal, home_team_goal) > 0); 2010 season refers to season = '2009/2010';",277 "SQL": "SELECT t3.team_long_name FROM League AS t1 INNER JOIN Match AS t2 ON t1.id = t2.league_id INNER JOIN Team AS t3 ON t2.away_team_api_id = t3.team_api_id WHERE t1.name = 'Scotland Premier League' AND t2.season = '2009/2010' AND t2.away_team_goal - t2.home_team_goal > 0 GROUP BY t2.away_team_api_id HAVING COUNT(*) = (SELECT MAX(won_matches_count) FROM (SELECT away_team_api_id, COUNT(*) AS won_matches_count FROM Match AS m INNER JOIN League AS l ON m.league_id = l.id WHERE l.name = 'Scotland Premier League' AND m.season = '2009/2010' AND m.away_team_goal - m.home_team_goal > 0 GROUP BY m.away_team_api_id))",278 "difficulty": "challenging",279 "index": 30280 },281 {282 "question_id": 1034,283 "db_id": "european_football_2",284 "question": "List the players' api id who had the highest above average overall ratings in 2010.",285 "evidence": "highest above average overall ratings refers to MAX(overall_rating); in 2010 refers to substr(date,1,4) = '2010';",286 "SQL": "SELECT p.player_api_id FROM Player_Attributes AS p WHERE SUBSTR(p.date, 1, 4) = '2010' AND p.overall_rating = (SELECT MAX(pa.overall_rating) FROM Player_Attributes AS pa WHERE SUBSTR(pa.date, 1, 4) = '2010')",287 "difficulty": "simple",288 "index": 31289 },290 {291 "question_id": 1090,292 "db_id": "european_football_2",293 "question": "What is the long passing score of the oldest player?",294 "evidence": "long passing score refers to long_passing; oldest player refers to oldest birthday;",295 "SQL": "SELECT T2.long_passing FROM Player AS T1 INNER JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id WHERE T1.birthday = (SELECT MIN(birthday) FROM Player)",296 "difficulty": "simple",297 "index": 32298 },299 {300 "question_id": 1123,301 "db_id": "european_football_2",302 "question": "What is the name of players with the highest potential?",303 "evidence": "highest potential refers to MAX(potential)",304 "SQL": "SELECT DISTINCT T1.player_name FROM Player AS T1 INNER JOIN Player_Attributes AS T2 ON T1.player_api_id = T2.player_api_id WHERE T2.potential = (SELECT MAX(potential) FROM Player_Attributes)",305 "difficulty": "simple",306 "index": 33307 },308 {309 "question_id": 1253,310 "db_id": "thrombosis_prediction",311 "question": "For the patient who has the highest Ig A within the normal range, what is his or her diagnosis?",312 "evidence": "highest Ig A within the normal range refers to MAX(IGA BETWEEN 80 AND 500);",313 "SQL": "SELECT T1.Diagnosis FROM Patient AS T1 INNER JOIN Laboratory AS T2 ON T1.ID = T2.ID WHERE T2.IGA = (SELECT MAX(IGA) FROM Laboratory WHERE IGA BETWEEN 80 AND 500)",314 "difficulty": "simple",315 "index": 34316 },317 {318 "question_id": 1284,319 "db_id": "thrombosis_prediction",320 "question": "For the patient with the highest lactate dehydrogenase in the normal range, when was his or her data first recorded?",321 "evidence": "highest lactate dehydrogenase in the normal range refers to MAX(LDH < 500); when the data first recorded refers to MIN(First Date);",322 "SQL": "SELECT T1.`First Date` FROM Patient AS T1 INNER JOIN Laboratory AS T2 ON T1.ID = T2.ID WHERE T2.LDH = (SELECT MAX(LDH) FROM Laboratory WHERE LDH < 500)",323 "difficulty": "moderate",324 "index": 35325 },326 {327 "question_id": 1290,328 "db_id": "thrombosis_prediction",329 "question": "What is the examination date of the patient whose albumin is the highest in the normal range?",330 "evidence": "examination date refers to Date; albumin is the highest in the normal range refers to MAX(ALB > 3.5 and ALB < 5.5);",331 "SQL": "SELECT Date FROM Laboratory WHERE ALB = (SELECT MAX(ALB) FROM Laboratory WHERE ALB BETWEEN 3.5 AND 5.5)",332 "difficulty": "simple",333 "index": 36334 },335 {336 "question_id": 1318,337 "db_id": "student_club",338 "question": "What is the event that has the highest attendance of the students from the Student_Club?",339 "evidence": "event with highest attendance refers to MAX(COUNT(link_to_event))",340 "SQL": "SELECT T1.event_name FROM event AS T1 INNER JOIN attendance AS T2 ON T1.event_id = T2.link_to_event GROUP BY T1.event_name HAVING COUNT(T2.link_to_event) = (SELECT MAX(attendance_count) FROM (SELECT COUNT(link_to_event) AS attendance_count FROM attendance GROUP BY link_to_event) AS subquery)",341 "difficulty": "simple",342 "index": 37343 },344 {345 "question_id": 1365,346 "db_id": "student_club",347 "question": "What are the expenses of the budget with the lowest remaining?",348 "evidence": "expense of budget refers to expense_description; lowest remaining refers to MIN(remaining)",349 "SQL": "SELECT T2.expense_description FROM budget AS T1 INNER JOIN expense AS T2 ON T1.budget_id = T2.link_to_budget WHERE T1.remaining = (SELECT MIN(remaining) FROM budget)",350 "difficulty": "simple",351 "index": 38352 },353 {354 "question_id": 1388,355 "db_id": "student_club",356 "question": "Which students manage to generate the highest income. State his/her full name along with the income source.",357 "evidence": "name of students means the full name; full name refers to first_name, last_name; generate the highest income refers to MAX(income.amount);",358 "SQL": "SELECT T1.first_name, T1.last_name, T2.source FROM member AS T1 INNER JOIN income AS T2 ON T1.member_id = T2.link_to_member WHERE T2.amount = (SELECT MAX(T4.amount) FROM member AS T3 INNER JOIN income AS T4 ON T3.member_id = T4.link_to_member)",359 "difficulty": "moderate",360 "index": 39361 },362 {363 "question_id": 1389,364 "db_id": "student_club",365 "question": "Which event has the lowest cost?",366 "evidence": "event refers to event_name where MIN(cost)",367 "SQL": "SELECT T1.event_name FROM event AS T1 INNER JOIN budget AS T2 ON T1.event_id = T2.link_to_event INNER JOIN expense AS T3 ON T2.budget_id = T3.link_to_budget WHERE T3.cost = (SELECT MIN(T6.cost) FROM event AS T4 INNER JOIN budget AS T5 ON T4.event_id = T5.link_to_event INNER JOIN expense AS T6 ON T5.budget_id = T6.link_to_budget)",368 "difficulty": "simple",369 "index": 40370 },371 {372 "question_id": 1517,373 "db_id": "debit_card_specializing",374 "question": "For the earliest customer, what segment did he/she have?",375 "evidence": "",376 "SQL": "SELECT T2.Segment FROM transactions_1k AS T1 INNER JOIN customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.Date = (SELECT MIN(Date) FROM transactions_1k)",377 "difficulty": "simple",378 "index": 41379 },380 {381 "question_id": 26,382 "db_id": "california_schools",383 "question": "State the names and full communication address of high schools in Monterey which has more than 800 free or reduced price meals for ages 5-17?",384 "evidence": "Full communication address should include Street, City, State and zip code if any.",385 "SQL": "SELECT DISTINCT T2.`School` AS `School Name`, T2.`Street`, T2.`City`, T2.`State`, T2.`Zip` FROM `schools` AS T2 JOIN `frpm` AS T1 ON T1.`CDSCode` = T2.`CDSCode` WHERE T2.`County` = 'Monterey' AND T1.`School Type` = 'High Schools (Public)' AND T1.`FRPM Count (Ages 5-17)` > 800;",386 "difficulty": "moderate",387 "index": 42388 },389 {390 "question_id": 35,391 "db_id": "california_schools",392 "question": "What is the administrator's email address of the chartered school with the fewest students enrolled in grades 1 through 12?",393 "evidence": "Charted school means `Charter School (Y/N)` = 1 in the table frpm; Students enrolled in grades 1 through 12 refers to `Enrollment (K-12)`",394 "SQL": "SELECT T2.AdmEmail1 FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T1.`Charter School (Y/N)` = 1 ORDER BY T1.`Enrollment (K-12)` ASC LIMIT 1",395 "difficulty": "moderate",396 "index": 43397 },398 {399 "question_id": 42,400 "db_id": "california_schools",401 "question": "What is the type of education offered in the school who scored the highest average in Math?",402 "evidence": "",403 "SQL": "SELECT s.`EdOpsName` FROM `satscores` AS sc JOIN `schools` AS s ON CAST(sc.`cds` AS INTEGER) = CAST(s.`CDSCode` AS INTEGER) WHERE sc.`rtype` = 'S' ORDER BY sc.`AvgScrMath` DESC LIMIT 1;",404 "difficulty": "simple",405 "index": 44406 },407 {408 "question_id": 71,409 "db_id": "california_schools",410 "question": "What are the distinct district codes for the School that does not offer a magnet program in the city of Fresno?",411 "evidence": "When magent is equal to 0 in the database, it means ths school doesn't offer a magnet program.",412 "SQL": "SELECT DISTINCT f.`District Code` FROM `frpm` AS f JOIN `schools` AS s ON f.`CDSCode` = s.`CDSCode` WHERE s.`City` = 'Fresno' AND s.`Magnet` = 0;",413 "difficulty": "simple",414 "index": 45415 },416 {417 "question_id": 111,418 "db_id": "financial",419 "question": "How many accounts were opened in Litomerice in 1996?",420 "evidence": "A2 refers to district name; Litomerice is one of district names.",421 "SQL": "SELECT COUNT(T2.account_id) FROM district AS T1 INNER JOIN account AS T2 ON T1.district_id = T2.district_id WHERE STRFTIME('%Y', T2.date) = '1996' AND T1.A2 = 'Litomerice'",422 "difficulty": "simple",423 "index": 46424 },425 {426 "question_id": 119,427 "db_id": "financial",428 "question": "For accounts in 1993 with statement issued after transaction, list the account ID, district name and district region.",429 "evidence": "Records about district names could be found in A2; A3 contains the information about regions. 'POPLATEK PO OBRATU' stands for issuance after transaction",430 "SQL": "SELECT T1.account_id, T2.A2, T2.A3 FROM account AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id WHERE T1.frequency = 'POPLATEK PO OBRATU' AND STRFTIME('%Y', T1.date)= '1993'",431 "difficulty": "moderate",432 "index": 47433 },434 {435 "question_id": 139,436 "db_id": "financial",437 "question": "How many high-level credit cards have \\\"OWNER\\\" type of disposition?",438 "evidence": "High-level credit cards refers to the cards with the gold type.",439 "SQL": "SELECT COUNT(T1.card_id) FROM card AS T1 INNER JOIN disp AS T2 ON T1.disp_id = T2.disp_id WHERE T1.type = 'gold' AND T2.type = 'OWNER'",440 "difficulty": "simple",441 "index": 48442 },443 {444 "question_id": 177,445 "db_id": "financial",446 "question": "What is the sum that client number 4's account has following transaction 851? Who owns this account, a man or a woman?",447 "evidence": "",448 "SQL": "SELECT T4.balance, T1.gender FROM client AS T1 INNER JOIN disp AS T2 ON T1.client_id = T2.client_id INNER JOIN account AS T3 ON T2.account_id =T3.account_id INNER JOIN trans AS T4 ON T3.account_id = T4.account_id WHERE T1.client_id = 4 AND T4.trans_id = 851",449 "difficulty": "simple",450 "index": 49451 },452 {453 "question_id": 180,454 "db_id": "financial",455 "question": "Please provide a list of client_ids and account_ids for clients who were born between 1983 and 1987 and whose account branch is in East Bohemia.",456 "evidence": "",457 "SQL": "SELECT DISTINCT c.client_id, a.account_id FROM client c JOIN disp d ON d.client_id = c.client_id JOIN account a ON a.account_id = d.account_id JOIN district dist ON dist.district_id = a.district_id WHERE CAST(STRFTIME('%Y', c.birth_date) AS INTEGER) BETWEEN 1983 AND 1987 AND dist.A3 = 'east Bohemia';",458 "difficulty": "moderate",459 "index": 50460 },461 {462 "question_id": 226,463 "db_id": "toxicology",464 "question": "What is the percentage of double bonds in the molecule TR008? Please provide your answer as a percentage with five decimal places.",465 "evidence": "double bond refers to bond_type = '='; TR008 is the molecule id; percentage = DIVIDE(SUM(bond_type = '='), COUNT(bond_id)) as percent where molecule_id = 'TR008'",466 "SQL": "SELECT ROUND(CAST(COUNT(CASE WHEN T.bond_type = '=' THEN T.bond_id ELSE NULL END) AS REAL) * 100 / COUNT(T.bond_id),5) FROM bond AS T WHERE T.molecule_id = 'TR008'",467 "difficulty": "moderate",468 "index": 51469 },470 {471 "question_id": 240,472 "db_id": "toxicology",473 "question": "List all the elements of the toxicology of the molecule \\\"TR004\\\".",474 "evidence": "TR004 is the molecule id;",475 "SQL": "SELECT DISTINCT T.element FROM atom AS T WHERE T.molecule_id = 'TR004'",476 "difficulty": "challenging",477 "index": 52478 },479 {480 "question_id": 251,481 "db_id": "toxicology",482 "question": "What is the least common element of all carcinogenic molecules? Least all elements if there is a tie.",483 "evidence": "label = '+' mean molecules are carcinogenic",484 "SQL": "WITH elem_counts AS ( SELECT a.element, COUNT(DISTINCT a.molecule_id) AS cnt FROM molecule AS m JOIN atom AS a ON m.molecule_id = a.molecule_id WHERE m.label = '+' GROUP BY a.element ) SELECT element FROM elem_counts WHERE cnt = (SELECT MIN(cnt) FROM elem_counts);",485 "difficulty": "moderate",486 "index": 53487 },488 {489 "question_id": 255,490 "db_id": "toxicology",491 "question": "What percentage of single bonds are found in molecules known to be carcinogenic, excluding those for which carcinogenicity is unknown? Please provide your answer to five decimal places.",492 "evidence": "single bond refers to bond_type = '-'; label = '+' mean molecules are carcinogenic; proportion = DIVIDE(SUM(label = '+') * 100, COUNT(bond_id)) where bond_type = '-'",493 "SQL": "SELECT ROUND(CAST(COUNT(CASE WHEN T2.label = '+' THEN T1.bond_id ELSE NULL END) AS REAL) * 100 / COUNT(T1.bond_id),5) FROM bond AS T1 INNER JOIN molecule AS T2 ON T1.molecule_id = T2.molecule_id WHERE T1.bond_type = '-'",494 "difficulty": "moderate",495 "index": 54496 },497 {498 "question_id": 268,499 "db_id": "toxicology",500 "question": "What are the elements for bond id TR001_10_11?",501 "evidence": "TR001_10_11 is the bond id;",502 "SQL": "SELECT T2.element FROM connected AS T1 INNER JOIN atom AS T2 ON T1.atom_id = T2.atom_id WHERE T1.bond_id = 'TR001_10_11'",503 "difficulty": "challenging",504 "index": 55505 },506 {507 "question_id": 272,508 "db_id": "toxicology",509 "question": "List the molecule ID of the non-carcinogenic molecule that contains at least one triple bond and the element carbon.",510 "evidence": "carbon refers to element = 'c'; triple bond refers to bond_type = '#'; label = '-' means molecules are non-carcinogenic",511 "SQL": "SELECT DISTINCT T2.molecule_id FROM atom AS T1 INNER JOIN molecule AS T2 ON T1.molecule_id = T2.molecule_id INNER JOIN bond AS T3 ON T2.molecule_id = T3.molecule_id WHERE T3.bond_type = '#' AND T1.element = 'c' AND T2.label = '-'",512 "difficulty": "moderate",513 "index": 56514 },515 {516 "question_id": 303,517 "db_id": "toxicology",518 "question": "How many double bonds does TR006 have and is it carcinogenic?",519 "evidence": "label = '+' mean molecules are carcinogenic; label = '-' means molecules are non-carcinogenic; double bond refers to bond_type = ' = ';",520 "SQL": "SELECT COUNT(T1.bond_id), T2.label FROM bond AS T1 INNER JOIN molecule AS T2 ON T1.molecule_id = T2.molecule_id WHERE T1.bond_type = '=' AND T2.molecule_id = 'TR006' GROUP BY T2.label",521 "difficulty": "moderate",522 "index": 57523 },524 {525 "question_id": 305,526 "db_id": "toxicology",527 "question": "Name all bonds with single bond types and what atoms are connected to the molecules.",528 "evidence": "A single bond refers to bond_type = '-'. Return the bond ID, atom ID 1, and atom ID 2, ensuring that atom ID 1 is smaller than atom ID 2.single bond refers to bond_type = '-'; return bond id, atom id 1 and atom id 2, and atom id 1 should be smaller than atom id 2",529 "SQL": "SELECT T1.bond_id, T2.atom_id, T2.atom_id2 FROM bond AS T1 INNER JOIN connected AS T2 ON T1.bond_id = T2.bond_id WHERE T1.bond_type = '-' AND T2.atom_id < T2.atom_id2",530 "difficulty": "simple",531 "index": 58532 },533 {534 "question_id": 310,535 "db_id": "toxicology",536 "question": "How many molecules have a double bond type and among these molecule, how many are labeled as carcinogenic compound?",537 "evidence": "double bond refers to bond_type = ' = '; label = '+' mean molecules are carcinogenic;",538 "SQL": "SELECT COUNT(DISTINCT m.`molecule_id`) AS double_bond_molecules, COUNT(DISTINCT CASE WHEN m.`label` = '+' THEN m.`molecule_id` END) AS carcinogenic_with_double_bond FROM bond b JOIN molecule m ON b.`molecule_id` = m.`molecule_id` WHERE b.`bond_type` = '=';",539 "difficulty": "moderate",540 "index": 59541 },542 {543 "question_id": 327,544 "db_id": "toxicology",545 "question": "Which non-carcinogenic molecules consisted more than 5 atoms?",546 "evidence": "label = '-' means molecules are non-carcinogenic; molecules consisted more than 5 atoms refers to COUNT(molecule_id) > 5",547 "SQL": "SELECT T.molecule_id FROM ( SELECT T1.molecule_id, COUNT(T2.atom_id) FROM molecule AS T1 INNER JOIN atom AS T2 ON T1.molecule_id = T2.molecule_id WHERE T1.label = '-' GROUP BY T1.molecule_id HAVING COUNT(T2.atom_id) > 5 ) t",548 "difficulty": "moderate",549 "index": 60550 },551 {552 "question_id": 353,553 "db_id": "card_games",554 "question": "List all the sets available in Italian translation. State the total number of cards per set.",555 "evidence": "Italian translation refers to language = 'Italian'; total number of card per set refers to totalSetSize;",556 "SQL": "SELECT T1.name, T1.totalSetSize FROM sets AS T1 INNER JOIN set_translations AS T2 ON T1.code = T2.setCode WHERE T2.language = 'Italian'",557 "difficulty": "simple",558 "index": 61559 },560 {561 "question_id": 378,562 "db_id": "card_games",563 "question": "What are the foiled cards that are incredibly powerful when paired with non foiled cards? List the IDs.",564 "evidence": "Incredibly powerful refers to both cardKingdomFoilId and cardKingdomId IS NOT Null;",565 "SQL": "SELECT id FROM cards WHERE cardKingdomId IS NOT NULL AND cardKingdomFoilId IS NOT NULL",566 "difficulty": "simple",567 "index": 62568 },569 {570 "question_id": 394,571 "db_id": "card_games",572 "question": "How many white bordered cards are powerful?",573 "evidence": "white bordered cards refer to borderColor = 'white'; powerful cards refers to cardKingdomFoilId is not null AND cardKingdomId is not null (replace); count distinct card ids",574 "SQL": "SELECT COUNT(id) FROM cards WHERE borderColor = 'white' AND cardKingdomId IS NOT NULL AND cardKingdomFoilId IS NOT NULL",575 "difficulty": "simple",576 "index": 63577 },578 {579 "question_id": 406,580 "db_id": "card_games",581 "question": "Lists by ID all Creature-type cards with legal status.",582 "evidence": "legal status refer to status = 'legal'; Creature-type cards refer to types LIKE '%Creature%'",583 "SQL": "SELECT DISTINCT c.id FROM `cards` c JOIN `legalities` l ON c.`uuid` = l.`uuid` WHERE l.`status` = 'Legal' AND c.types LIKE '%Creature%'",584 "difficulty": "simple",585 "index": 64586 },587 {588 "question_id": 416,589 "db_id": "card_games",590 "question": "What percentage of cards without power are in French?",591 "evidence": "in French refers to language = 'French'; cards without power refers to power IS NULL OR power = '*'; percentage = DIVIDE(COUNT(language = 'French' and power is NULL or power = '*'), COUNT( power is NULL or power = '*'))*100",592 "SQL": "SELECT 100.0 * COUNT(DISTINCT CASE WHEN fd.`language` = 'French' THEN c.`uuid` END) / COUNT(DISTINCT c.`uuid`) AS percentage_french FROM `cards` AS c LEFT JOIN `foreign_data` AS fd ON c.`uuid` = fd.`uuid` WHERE c.`power` IS NULL OR c.`power` = '*';",593 "difficulty": "challenging",594 "index": 65595 },596 {597 "question_id": 428,598 "db_id": "card_games",599 "question": "What is the name of set number 5 and its translation?",600 "evidence": "set number 5 refers to set id = 5",601 "SQL": "SELECT s.name, st.translation FROM sets s LEFT JOIN set_translations st ON s.code = st.setCode WHERE s.id = 5;",602 "difficulty": "simple",603 "index": 66604 },605 {606 "question_id": 442,607 "db_id": "card_games",608 "question": "Mention the base set size and set code of the set that was in block named \\\"Masques\\\" and \\\"Mirage\\\".",609 "evidence": "",610 "SQL": "SELECT `baseSetSize`, `code` FROM `sets` WHERE `block` IN ('Masques', 'Mirage');",611 "difficulty": "simple",612 "index": 67613 },614 {615 "question_id": 445,616 "db_id": "card_games",617 "question": "What are the language, flavor text, and card type of the cards that have a Color Pie watermark?",618 "evidence": "return columns should be card name, type, language, and flavor text",619 "SQL": "SELECT c.`name`, c.`type`, f.`language`, f.`flavorText` FROM `cards` AS c JOIN `foreign_data` AS f ON f.`uuid` = c.`uuid` WHERE c.`watermark` = 'colorpie';",620 "difficulty": "simple",621 "index": 68622 },623 {624 "question_id": 450,625 "db_id": "card_games",626 "question": "Of all the cards that are designed by Aaron Miller, how many of them are incredibly powerful?",627 "evidence": "designed by Aaron Miller refers to artist = 'Aaron Miller'; are icredibily powerful refers to cardKingdomFoilId is not null AND cardKingdomId is not null",628 "SQL": "SELECT SUM(CASE WHEN artist = 'Aaron Miller' AND cardKingdomFoilId IS NOT NULL AND cardKingdomId IS NOT NULL THEN 1 ELSE 0 END) FROM cards",629 "difficulty": "moderate",630 "index": 69631 },632 {633 "question_id": 484,634 "db_id": "card_games",635 "question": "Please list the Italian names of the cards in the set Coldsnap with the highest converted mana cost.",636 "evidence": "card set Coldsnap refers to name = 'Coldsnap'; Italian refers to language = 'Italian'; highest converted mana cost refers to MAX(convertedManaCost)",637 "SQL": "SELECT DISTINCT fd.`name` FROM `cards` AS c JOIN `foreign_data` AS fd ON fd.`uuid` = c.`uuid` INNER JOIN sets T3 ON T3.code = c.setCode WHERE T3.name ='Coldsnap' AND fd.`language` = 'Italian' AND c.`convertedManaCost` = ( SELECT MAX(`convertedManaCost`) FROM `cards` T1 INNER JOIN sets T2 ON T2.code = T1.setCode WHERE T2.name ='Coldsnap' );",638 "difficulty": "moderate",639 "index": 70640 },641 {642 "question_id": 491,643 "db_id": "card_games",644 "question": "What's the magic card market name for the set which was released on 2017/6/9?",645 "evidence": "magic card market name refers to mcmName",646 "SQL": "SELECT mcmName FROM sets WHERE releaseDate = '2017-06-09'",647 "difficulty": "simple",648 "index": 71649 },650 {651 "question_id": 503,652 "db_id": "card_games",653 "question": "What was the expansion type for the set which card \\\"Samite Pilgrim\\\" in it?",654 "evidence": "expansion type refers to type; card \\\"Samite Pilgrim\\\" refers to name = 'Samite Pilgrim'",655 "SQL": "SELECT type FROM sets WHERE code IN ( SELECT setCode FROM cards WHERE name = 'Samite Pilgrim' )",656 "difficulty": "simple",657 "index": 72658 },659 {660 "question_id": 522,661 "db_id": "card_games",662 "question": "Which cards are ranked 1st on EDHRec? List all of the cards name and its banned play format.",663 "evidence": "ranked 1st on EDHRec refers to edhrecRank = 1; banned refers to status = 'Banned'; play format refers to format; cards name refers to name",664 "SQL": "SELECT T1.name, T2.format FROM cards AS T1 INNER JOIN legalities AS T2 ON T2.uuid = T1.uuid WHERE T1.edhrecRank = 1 AND T2.status = 'Banned' GROUP BY T1.name, T2.format",665 "difficulty": "moderate",666 "index": 73667 },668 {669 "question_id": 528,670 "db_id": "card_games",671 "question": "List the names of all the cards in the set Hour of Devastation and find the formats in which these cards are legal.",672 "evidence": "the set Hour of Devastation refers to set.name = 'Hour of Devastation'; names of all the cards in the set refers to cards.name; legal cards refers to status = 'Legal'; the formats refers to format",673 "SQL": "SELECT DISTINCT T2.name , CASE WHEN T1.status = 'Legal' THEN T1.format ELSE NULL END FROM legalities AS T1 INNER JOIN cards AS T2 ON T2.uuid = T1.uuid WHERE T2.setCode IN ( SELECT code FROM sets WHERE name = 'Hour of Devastation' )",674 "difficulty": "challenging",675 "index": 74676 },677 {678 "question_id": 543,679 "db_id": "codebase_community",680 "question": "For the post that got the most number of answers owned by csgillespie, how many answers did it get?",681 "evidence": "\\\"csgillespie\\\" is the DisplayName of user; the most number of answer refers to Max(AnswerCount)",682 "SQL": "SELECT MAX(T1.AnswerCount) FROM posts AS T1 INNER JOIN users AS T2 ON T1.OwnerUserId = T2.Id WHERE T2.DisplayName = 'csgillespie'",683 "difficulty": "simple",684 "index": 75685 },686 {687 "question_id": 546,688 "db_id": "codebase_community",689 "question": "Please list the display names of all the users who owns a post that is well-finished.",690 "evidence": "the post that is well-finished refers to ClosedDate IS NOT Null",691 "SQL": "SELECT DISTINCT u.DisplayName FROM `posts` AS p JOIN `users` AS u ON p.OwnerUserId = u.Id WHERE p.ClosedDate IS NOT NULL;",692 "difficulty": "simple",693 "index": 76694 },695 {696 "question_id": 562,697 "db_id": "codebase_community",698 "question": "For the post which got 1910 view counts, how many comments does it get?",699 "evidence": "",700 "SQL": "SELECT COUNT(T1.Id) FROM posts AS T1 INNER JOIN comments AS T2 ON T1.Id = T2.PostId WHERE T1.ViewCount = 1910",701 "difficulty": "simple",702 "index": 77703 },704 {705 "question_id": 569,706 "db_id": "codebase_community",707 "question": "Give the total number of votes for all posts that are about data visualization.",708 "evidence": "About data visualization is the Title that contains 'data visualization';",709 "SQL": "SELECT COUNT(T1.Id) FROM posts AS T1 INNER JOIN votes AS T2 ON T1.Id = T2.PostId WHERE T1.Title LIKE '%data visualization%'",710 "difficulty": "simple",711 "index": 78712 },713 {714 "question_id": 590,715 "db_id": "codebase_community",716 "question": "Which user IDs have the lowest number of views",717 "evidence": "lowest views refers to Min(Views)",718 "SQL": "SELECT Id FROM users WHERE Views = ( SELECT MIN(Views) FROM users )",719 "difficulty": "simple",720 "index": 79721 },722 {723 "question_id": 598,724 "db_id": "codebase_community",725 "question": "What is the percentage difference of student badges given during 2010 and 2011?",726 "evidence": "student badges refers to badge's name = 'Student'; during 2010 refers to Year(Date) = 2010; during 2011 refers to Year(Date) = 2011; percentage difference = Subtract (Divide(Count(Name where Year(Date) = 2010), Count (Name)) *100, Divide(Count(Name where Year(Date) = 2011), Count(Name)) * 100)",727 "SQL": "SELECT CAST(SUM(IIF(STRFTIME('%Y', Date) = '2010', 1, 0)) AS REAL) * 100 / COUNT(Id) - CAST(SUM(IIF(STRFTIME('%Y', Date) = '2011', 1, 0)) AS REAL) * 100 / COUNT(Id) FROM badges WHERE Name = 'Student'",728 "difficulty": "challenging",729 "index": 80730 },731 {732 "question_id": 602,733 "db_id": "codebase_community",734 "question": "List out all post ID with score more than 60 and list out all the user ID that created these post. Do not include posts that do not have an associated owner.",735 "evidence": "score more than 60 refers to Score > 60",736 "SQL": "SELECT Id AS PostId, OwnerUserId FROM posts WHERE Score > 60 AND OwnerUserId IS NOT NULL;",737 "difficulty": "simple",738 "index": 81739 },740 {741 "question_id": 605,742 "db_id": "codebase_community",743 "question": "How many users obtained the \\\"Announcer\\\" badge?",744 "evidence": "\\\"Announcer\\\" is the Name of badge; user refers to UserId",745 "SQL": "SELECT COUNT(DISTINCT `UserId`) FROM `badges` WHERE `Name` = 'Announcer';",746 "difficulty": "simple",747 "index": 82748 },749 {750 "question_id": 607,751 "db_id": "codebase_community",752 "question": "How many positive comments are there on the list?",753 "evidence": "Positive comment refers to score > 60",754 "SQL": "SELECT COUNT(id) FROM comments WHERE score > 60",755 "difficulty": "simple",756 "index": 83757 },758 {759 "question_id": 610,760 "db_id": "codebase_community",761 "question": "What are the name of badge that users who have the highest reputation obtained?",762 "evidence": "highest reputation refers to Max(Reputation); user refers to UserId",763 "SQL": "SELECT DISTINCT b.`Name` FROM `users` u JOIN `badges` b ON b.`UserId` = u.`Id` WHERE u.`Reputation` = (SELECT MAX(`Reputation`) FROM `users`);",764 "difficulty": "simple",765 "index": 84766 },767 {768 "question_id": 620,769 "db_id": "codebase_community",770 "question": "For each user who obtained a badge at exactly 7/19/2010 7:39:08 PM, state the number of views they have, ensuring each user appears only once.",771 "evidence": "on 7/19/2010 7:39:08 PM refers to Date = '2010-07-19 19:39:08.0'",772 "SQL": "SELECT DISTINCT u.Id, u.Views FROM `users` u JOIN `badges` b ON u.Id = b.UserId WHERE b.Date = '2010-07-19 19:39:08.0';",773 "difficulty": "simple",774 "index": 85775 },776 {777 "question_id": 641,778 "db_id": "codebase_community",779 "question": "How many users received commentator badges in 2014?",780 "evidence": "Commentator is the name of the badge; year(Date) = 2014;",781 "SQL": "SELECT COUNT(Id) FROM badges WHERE Name = 'Commentator' AND STRFTIME('%Y', Date) = '2014'",782 "difficulty": "simple",783 "index": 86784 },785 {786 "question_id": 646,787 "db_id": "codebase_community",788 "question": "Describe the post title which got positive comments and display names of the users who posted those comments.",789 "evidence": "positive comments refer to Score > 60;",790 "SQL": "SELECT p.`Title`, c.`UserDisplayName` FROM `posts` AS p JOIN `comments` AS c ON p.`Id` = c.`PostId` WHERE c.`Score` > 60;",791 "difficulty": "simple",792 "index": 87793 },794 {795 "question_id": 648,796 "db_id": "codebase_community",797 "question": "Provide the users' display names and available website URLs of the post with favorite count of more than 150.",798 "evidence": "favorite count of more than 150 refers to FavoriteCount > 150; available website URLs refers to WebsiteUrl is not NULL",799 "SQL": " SELECT DISTINCT u.`DisplayName`, u.`WebsiteUrl` FROM `posts` p JOIN `users` u ON u.`Id` = p.`OwnerUserId` WHERE p.`FavoriteCount` > 150 AND u.`WebsiteUrl` IS NOT NULL;",800 "difficulty": "simple",801 "index": 88802 },803 {804 "question_id": 675,805 "db_id": "codebase_community",806 "question": "How many users whose reputations are higher than 2000 and the number of views is higher than 1000?",807 "evidence": "reputations are higher than 2000 refer to Reputation > 2000; number of views is higher than 1000 refers to Views > 1000;",808 "SQL": "SELECT COUNT(id) FROM users WHERE Reputation > 2000 AND Views > 1000",809 "difficulty": "simple",810 "index": 89811 },812 {813 "question_id": 717,814 "db_id": "superhero",815 "question": "Please list all the superpowers of 3-D Man.",816 "evidence": "3-D Man refers to superhero_name = '3-D Man'; superpowers refers to power_name",817 "SQL": "SELECT T3.power_name FROM superhero AS T1 INNER JOIN hero_power AS T2 ON T1.id = T2.hero_id INNER JOIN superpower AS T3 ON T2.power_id = T3.id WHERE T1.superhero_name = '3-D Man'",818 "difficulty": "simple",819 "index": 90820 },821 {822 "question_id": 740,823 "db_id": "superhero",824 "question": "How many female superheroes have a strength value of 100?",825 "evidence": "female refers to gender = 'Female'; strength value of 100 refers to attribute_name = 'Strength' AND attribute_value = 100",826 "SQL": "SELECT COUNT(T1.id) FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id INNER JOIN attribute AS T3 ON T2.attribute_id = T3.id INNER JOIN gender AS T4 ON T1.gender_id = T4.id WHERE T3.attribute_name = 'Strength' AND T2.attribute_value = 100 AND T4.gender = 'Female'",827 "difficulty": "moderate",828 "index": 91829 },830 {831 "question_id": 772,832 "db_id": "superhero",833 "question": "List the eyes, hair and skin colour of all female superheroes published by Dark Horse Comics.",834 "evidence": "eyes refers to eye_colour; hair refers to hair_colour; skin colour refers to skin_colour; female superheroes refers to gender = 'Female'; published by Dark Horse Comics refers to publisher_name = 'Dark Horse Comics';",835 "SQL": "SELECT c_eye.colour AS eye_colour, c_hair.colour AS hair_colour, c_skin.colour AS skin_colour FROM superhero s JOIN publisher p ON p.id = s.publisher_id JOIN gender g ON g.id = s.gender_id LEFT JOIN colour c_eye ON c_eye.id = s.eye_colour_id LEFT JOIN colour c_hair ON c_hair.id = s.hair_colour_id LEFT JOIN colour c_skin ON c_skin.id = s.skin_colour_id WHERE p.publisher_name = 'Dark Horse Comics' AND g.gender = 'Female';",836 "difficulty": "challenging",837 "index": 92838 },839 {840 "question_id": 781,841 "db_id": "superhero",842 "question": "Provide the heights of the heroes whose eye colours are amber.",843 "evidence": "heights of the heroes refers to height_cm; eye colours are amber refers to colour.colour = 'Amber' WHERE eye_colour_id = colour.id;",844 "SQL": "SELECT T1.height_cm FROM superhero AS T1 INNER JOIN colour AS T2 ON T1.eye_colour_id = T2.id WHERE T2.colour = 'Amber'",845 "difficulty": "simple",846 "index": 93847 },848 {849 "question_id": 801,850 "db_id": "superhero",851 "question": "Find the ratio between male superheroes and female superheroes.",852 "evidence": "ratio = DIVIDE(SUM(gender_id = 1) / SUM(gender_id = 2)); male superheroes refers to gender = 'Female'; female superheroes refers to gender = 'Male';",853 "SQL": "SELECT CAST(COUNT(CASE WHEN T2.gender = 'Male' THEN T1.id ELSE NULL END) AS REAL) / COUNT(CASE WHEN T2.gender = 'Female' THEN T1.id ELSE NULL END) FROM superhero AS T1 INNER JOIN gender AS T2 ON T1.gender_id = T2.id",854 "difficulty": "moderate",855 "index": 94856 },857 {858 "question_id": 824,859 "db_id": "superhero",860 "question": "Identify superheroes who can control wind and list their names in alphabetical order.",861 "evidence": "superheroes refers to superhero_name; can control wind refers to power_name = 'Wind Control';",862 "SQL": "SELECT T1.superhero_name FROM superhero AS T1 INNER JOIN hero_power AS T2 ON T1.id = T2.hero_id INNER JOIN superpower AS T3 ON T2.power_id = T3.id WHERE T3.power_name = 'Wind Control' ORDER BY T1.superhero_name",863 "difficulty": "moderate",864 "index": 95865 },866 {867 "question_id": 829,868 "db_id": "superhero",869 "question": "Which publisher created more superheroes: DC or Marvel Comics? Find the difference in the number of superheroes.",870 "evidence": "DC refers to publisher_name = 'DC Comics'; Marvel Comics refers to publisher_name = 'Marvel Comics'; difference = ABS(SUBTRACT(SUM(publisher_name = 'DC Comics'), SUM(publisher_name = 'Marvel Comics')));",871 "SQL": "WITH counts AS ( SELECT p.publisher_name, COUNT(*) AS cnt FROM superhero s JOIN publisher p ON s.publisher_id = p.id WHERE p.publisher_name IN ('DC Comics', 'Marvel Comics') GROUP BY p.publisher_name ) SELECT CASE WHEN (SELECT cnt FROM counts WHERE publisher_name = 'DC Comics') > (SELECT cnt FROM counts WHERE publisher_name = 'Marvel Comics') THEN 'DC Comics' ELSE 'Marvel Comics' END AS publisher_with_more_superheroes, ABS((SELECT cnt FROM counts WHERE publisher_name = 'DC Comics') - (SELECT cnt FROM counts WHERE publisher_name = 'Marvel Comics')) AS difference;",872 "difficulty": "challenging",873 "index": 96874 },875 {876 "question_id": 833,877 "db_id": "superhero",878 "question": "Name the superhero, otherwise known as Charles Chandler.",879 "evidence": "name the superhero refers to superhero_name; Charles Chandler is the full name of superhero;",880 "SQL": "SELECT superhero_name FROM superhero WHERE full_name = 'Charles Chandler'",881 "difficulty": "simple",882 "index": 97883 },884 {885 "question_id": 840,886 "db_id": "superhero",887 "question": "List the attribute value of the superhero named Aquababy.",888 "evidence": "",889 "SQL": "SELECT T2.attribute_value FROM superhero AS T1 INNER JOIN hero_attribute AS T2 ON T1.id = T2.hero_id WHERE T1.superhero_name = 'Aquababy'",890 "difficulty": "simple",891 "index": 98892 },893 {894 "question_id": 846,895 "db_id": "formula_1",896 "question": "Please list the reference names of the drivers who are eliminated in the first period in race number 20.",897 "evidence": "driver reference name refers to driverRef; first qualifying period refers to q1; race number refers to raceId;",898 "SQL": "SELECT d.driverRef FROM qualifying AS q JOIN drivers AS d ON d.driverId = q.driverId WHERE q.raceId = 20 AND q.q2 IS NULL;",899 "difficulty": "moderate",900 "index": 99901 },902 {903 "question_id": 855,904 "db_id": "formula_1",905 "question": "Where can I find the information about the races held on Sepang International Circuit?",906 "evidence": "information about races refers to the url of races;",907 "SQL": "SELECT DISTINCT R.`url` FROM `races` AS R JOIN `circuits` AS C ON R.`circuitId` = C.`circuitId` WHERE C.`name` = 'Sepang International Circuit';",908 "difficulty": "simple",909 "index": 100910 },911 {912 "question_id": 864,913 "db_id": "formula_1",914 "question": "For the race happened on 2015/11/29, how many drivers finished the game?",915 "evidence": "game and race are synonyms; drivers who finished the race refers to the status is 'finished' or '+%Lap%'",916 "SQL": "SELECT COUNT(*) FROM `results` r JOIN `races` ra ON ra.`raceId` = r.`raceId` JOIN `status` s ON s.`statusId` = r.`statusId` WHERE ra.`date` = '2015-11-29' AND (s.`status` = 'Finished' OR s.`status` LIKE '+%Lap%');",917 "difficulty": "simple",918 "index": 101919 },920 {921 "question_id": 888,922 "db_id": "formula_1",923 "question": "In which country was the first European Grand Prix hosted? Name the circuit and location.",924 "evidence": "the first refers to min(year);",925 "SQL": "SELECT c.country, c.name AS circuit, c.location FROM circuits c JOIN races r ON r.circuitId = c.circuitId WHERE r.name = 'European Grand Prix' ORDER BY r.year ASC LIMIT 1;",926 "difficulty": "simple",927 "index": 102928 },929 {930 "question_id": 898,931 "db_id": "formula_1",932 "question": "How old is the youngest Japanese driver? What is his name?",933 "evidence": "date of birth refers to drivers.dob; The larger the birthday value, the younger the person is, and vice versa; Japanese refers to nationality = 'Japanese'; age = YEAR(CURRENT_TIMESTAMP) - YEAR(dob);",934 "SQL": "SELECT STRFTIME('%Y', CURRENT_TIMESTAMP) - STRFTIME('%Y', dob), forename , surname FROM drivers WHERE nationality = 'Japanese' ORDER BY dob DESC LIMIT 1",935 "difficulty": "simple",936 "index": 103937 },938 {939 "question_id": 899,940 "db_id": "formula_1",941 "question": "List circuits which host 4 f1 races from year 1990 to 2000.",942 "evidence": "from year 1990 to 2000 refers to year(date) between 1990 and 2000;",943 "SQL": "SELECT DISTINCT T1.name FROM circuits AS T1 INNER JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE STRFTIME('%Y', T2.date) BETWEEN '1990' AND '2000' GROUP BY T1.name HAVING COUNT(T2.raceId) = 4",944 "difficulty": "moderate",945 "index": 104946 },947 {948 "question_id": 922,949 "db_id": "formula_1",950 "question": "What time did the the 2010's Formula_1 race took place on the Abu Dhabi Circuit?",951 "evidence": "",952 "SQL": "SELECT T2.date, T2.time FROM circuits AS T1 INNER JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE T2.year = 2010 AND T2.name = 'Abu Dhabi Grand Prix'",953 "difficulty": "simple",954 "index": 105955 },956 {957 "question_id": 928,958 "db_id": "formula_1",959 "question": "Which driver ranked the first in the Canadian Grand Prix in 2007? Please give his reference name.",960 "evidence": "reference name refers to driverRef; Canadian Grand Prix refers to races.name = 'Canadian Grand Prix';",961 "SQL": "SELECT d.`driverRef` FROM `races` r JOIN `results` res ON res.`raceId` = r.`raceId` JOIN `drivers` d ON d.`driverId` = res.`driverId` WHERE r.`name` = 'Canadian Grand Prix' AND r.`year` = 2007 AND res.`position` = 1;",962 "difficulty": "moderate",963 "index": 106964 },965 {966 "question_id": 935,967 "db_id": "formula_1",968 "question": "How many drivers managed to finish the race in the 2008 Australian Grand Prix?",969 "evidence": "managed to finish the race refers to the status is 'finished' or '+%Lap%'",970 "SQL": "SELECT COUNT(*) FROM `races` r JOIN `results` res ON res.`raceId` = r.`raceId` JOIN `status` s ON s.`statusId` = res.`statusId` WHERE r.`year` = 2008 AND r.`name` = 'Australian Grand Prix' AND (s.`status` = 'Finished' OR s.`status` LIKE '+%Lap%');",971 "difficulty": "simple",972 "index": 107973 },974 {975 "question_id": 938,976 "db_id": "formula_1",977 "question": "Who was the champion of 2008's Australian Grand Prix and where can I know more about him?",978 "evidence": "only champion's finished time is represented by 'HH:MM:SS.mmm'; where can I know more refers to url",979 "SQL": "SELECT T1.forename, T1.surname, T1.url FROM drivers AS T1 INNER JOIN results AS T2 ON T1.driverId = T2.driverId INNER JOIN races AS T3 ON T3.raceId = T2.raceId WHERE T3.name = 'Australian Grand Prix' AND T2.time LIKE '_:%:__.___' AND T3.year = 2008",980 "difficulty": "moderate",981 "index": 108982 },983 {984 "question_id": 968,985 "db_id": "formula_1",986 "question": "What is reference name of Robert Kubica?",987 "evidence": "reference name refers to driverRef;",988 "SQL": "SELECT driverRef FROM drivers WHERE forename = 'Robert' AND surname = 'Kubica'",989 "difficulty": "simple",990 "index": 109991 },992 {993 "question_id": 970,994 "db_id": "formula_1",995 "question": "List out driver IDs of top 3 German drivers who were born from 1980-1990 and have the earliest lap time.",996 "evidence": "born from 1980-1990 refers to year(dob) between 1980 and 1990; earliest lap time refers to Min(time);",997 "SQL": "SELECT d.driverId FROM `drivers` AS d JOIN `lapTimes` AS l ON d.driverId = l.driverId WHERE d.nationality = 'German' AND CAST(strftime('%Y', d.dob) AS INTEGER) BETWEEN 1980 AND 1990 GROUP BY d.driverId ORDER BY MIN(l.`milliseconds`) LIMIT 3;",998 "difficulty": "moderate",999 "index": 1101000 },1001 {1002 "question_id": 985,1003 "db_id": "formula_1",1004 "question": "Among all the drivers who have participated in any year of the French Grand Prix throughout its history, which driver recorded the slowest time on the 3rd lap in any of these races? Please specify the driver id.",1005 "evidence": "slowest time refers to Max(time);",1006 "SQL": "SELECT d.driverId FROM lapTimes AS lt JOIN races AS r ON r.raceId = lt.raceId JOIN drivers AS d ON d.driverId = lt.driverId WHERE r.name = 'French Grand Prix' AND lt.lap = 3 ORDER BY (CAST(substr(lt.time , 1, instr(lt.time , ':') -1) AS INTEGER) * 60 * 1000) + (CAST(substr(lt.time , instr(lt.time , ':')+1) AS REAL) * 1000) DESC LIMIT 1;",1007 "difficulty": "simple",1008 "index": 1111009 },1010 {1011 "question_id": 987,1012 "db_id": "formula_1",1013 "question": "What is the average fastest lap time, in milliseconds, of the top five finishers in the 2006 United States Grand Prix?",1014 "evidence": "top 5 refers to position <=5;",1015 "SQL": "SELECT AVG( CAST(substr(`fastestLapTime`,1,instr(`fastestLapTime`,':')-1) AS integer)*60000 + CAST(substr(`fastestLapTime`,instr(`fastestLapTime`,':')+1) AS real) * 1000 ) FROM `results` r JOIN `races` ra ON r.`raceId` = ra.`raceId` WHERE ra.`year` = 2006 AND ra.`name` = 'United States Grand Prix' AND r.`position` <= 5;",1016 "difficulty": "simple",1017 "index": 1121018 },1019 {1020 "question_id": 1004,1021 "db_id": "formula_1",1022 "question": "How many race wins was achieved by the oldest racer? Indicate his/her full name.",1023 "evidence": "oldest racer refers to MIN(dob); the output should be in the format forename, surname, and the number of wins",1024 "SQL": "WITH oldest AS ( SELECT `driverId`, `forename`, `surname` FROM `drivers` WHERE `dob` IS NOT NULL ORDER BY `dob` ASC LIMIT 1 ) SELECT o.`forename`, o.`surname`, COUNT(r.`resultId`) AS wins FROM oldest o LEFT JOIN `results` r ON r.`driverId` = o.`driverId` AND r.`position` = 1 GROUP BY o.`driverId`;",1025 "difficulty": "simple",1026 "index": 1131027 },1028 {1029 "question_id": 1039,1030 "db_id": "european_football_2",1031 "question": "Find the average number of long-shot done by Ahmed Samir Farag.",1032 "evidence": "average number of long shot = DIVIDE(SUM(long_shots), COUNT(player_fifa_api_id));",1033 "SQL": "SELECT AVG(pa.`long_shots`) AS avg_long_shots FROM `Player` AS p JOIN `Player_Attributes` AS pa ON p.`player_api_id` = pa.`player_api_id` WHERE p.`player_name` = 'Ahmed Samir Farag';",1034 "difficulty": "simple",1035 "index": 1141036 },1037 {1038 "question_id": 1042,1039 "db_id": "european_football_2",1040 "question": "List the name of leagues in which the average goals by the home team is higher than the away team in the 2009/2010 season.",1041 "evidence": "name of league refers to League.name; average goals by the home team is higher than the away team = AVG(home_team_goal) > AVG(away_team_goal); AVG(xx_goal) = SUM(xx_goal) / COUNT(DISTINCT Match.id); 2009/2010 season refers to season = '2009/2010'",1042 "SQL": "SELECT t1.name FROM League AS t1 INNER JOIN Match AS t2 ON t1.id = t2.league_id WHERE t2.season = '2009/2010' GROUP BY t1.name HAVING (CAST(SUM(t2.home_team_goal) AS REAL) / COUNT(DISTINCT t2.id)) - (CAST(SUM(t2.away_team_goal) AS REAL) / COUNT(DISTINCT t2.id)) > 0",1043 "difficulty": "challenging",1044 "index": 1151045 },1046 {1047 "question_id": 1048,1048 "db_id": "european_football_2",1049 "question": "What is the overall rating of the football player Gabriel Tamas in year 2011?",1050 "evidence": "in year 2011 refers to strftime('%Y', date) = '2011';",1051 "SQL": "SELECT t2.overall_rating FROM Player AS t1 INNER JOIN Player_Attributes AS t2 ON t1.player_api_id = t2.player_api_id WHERE t1.player_name = 'Gabriel Tamas' AND strftime('%Y', t2.date) = '2011'",1052 "difficulty": "simple",1053 "index": 1161054 },1055 {1056 "question_id": 1056,1057 "db_id": "european_football_2",1058 "question": "What is the name of the football league in the country of Netherlands?",1059 "evidence": "name of the football league refers to League.name;",1060 "SQL": "SELECT t2.name FROM Country AS t1 INNER JOIN League AS t2 ON t1.id = t2.country_id WHERE t1.name = 'Netherlands'",1061 "difficulty": "simple",1062 "index": 1171063 },1064 {1065 "question_id": 1082,1066 "db_id": "european_football_2",1067 "question": "Please list the leagues from Germany.",1068 "evidence": "Germany refers to Country.name = 'Germany';",1069 "SQL": "SELECT t2.name FROM Country AS t1 INNER JOIN League AS t2 ON t1.id = t2.country_id WHERE t1.name = 'Germany'",1070 "difficulty": "simple",1071 "index": 1181072 },1073 {1074 "question_id": 1118,1075 "db_id": "european_football_2",1076 "question": "List down the name of players who are 35 years old and above.",1077 "evidence": "35 years old and above refers to datetime(CURRENT_TIMESTAMP,'localtime') - datetime(birthday) >= 35",1078 "SQL": "SELECT player_name FROM Player WHERE CAST((JULIANDAY('now') - JULIANDAY(birthday)) AS REAL) / 365 >= 35",1079 "difficulty": "simple",1080 "index": 1191081 },1082 {1083 "question_id": 1145,1084 "db_id": "european_football_2",1085 "question": "Which top 4 leagues had the most games in the 2015-2016 season?",1086 "evidence": "in the 2015-2016 season refers to season = '2015/2016'; league with most games refers to League.name where MAX(COUNT(id))",1087 "SQL": "SELECT t1.name FROM League AS t1 INNER JOIN Match AS t2 ON t1.id = t2.league_id WHERE t2.season = '2015/2016' GROUP BY t1.name ORDER BY COUNT(t2.id) DESC LIMIT 4",1088 "difficulty": "simple",1089 "index": 1201090 },1091 {1092 "question_id": 1157,1093 "db_id": "thrombosis_prediction",1094 "question": "For patients with severe degree of thrombosis, list their ID, sex and disease the patient is diagnosed with.",1095 "evidence": "severe degree of thrombosis refers to thrombosis = 2; disease refers to diagnosis;",1096 "SQL": "SELECT DISTINCT T1.ID, T1.SEX, T1.Diagnosis FROM Patient AS T1 INNER JOIN Examination AS T2 ON T1.ID = T2.ID WHERE T2.Thrombosis = 2",1097 "difficulty": "simple",1098 "index": 1211099 },1100 {1101 "question_id": 1173,1102 "db_id": "thrombosis_prediction",1103 "question": "What is the most common illness that doctors identified among the patients whose lab work was done between 1/1/1985, and 12/31/1995? List the illness name and the number of occurrences.",1104 "evidence": "the most common illness refers to MAX(COUNT(Diagnosis)); lab work between 1/1/1985 and 12/31/1995 refers to `Date` between '1985-01-01' and '1995-12-31 '",1105 "SQL": "WITH RECURSIVE split(id, rest, diagnosis_single) AS ( SELECT id, diagnosis || ',', '' FROM patient UNION ALL SELECT id, SUBSTR(rest, INSTR(rest, ',') + 1), TRIM(SUBSTR(rest, 1, INSTR(rest, ',') - 1)) FROM split WHERE rest <> '' AND INSTR(rest, ',') > 0 ), patient_dia as ( SELECT id, diagnosis_single FROM split WHERE diagnosis_single <> '' order by id ) SELECT diagnosis_single, COUNT(*) FROM patient_dia P JOIN ( SELECT DISTINCT `ID` FROM `Laboratory` WHERE `Date` BETWEEN '1985-01-01' AND '1995-12-31' ) L ON P.`ID` = L.`ID` GROUP BY P.diagnosis_single ORDER BY COUNT(*) DESC LIMIT 1;",1106 "difficulty": "challenging",1107 "index": 1221108 },1109 {1110 "question_id": 1178,1111 "db_id": "thrombosis_prediction",1112 "question": "What was the gender of the first AORTITIS diagnosed patient?",1113 "evidence": "gender means SEX; 'AORTITIS' refers to Diagnosis;",1114 "SQL": "SELECT SEX FROM Patient WHERE Diagnosis = 'AORTITIS' AND `First Date` IS NOT NULL ORDER BY `First Date` ASC LIMIT 1",1115 "difficulty": "simple",1116 "index": 1231117 },1118 {1119 "question_id": 1200,1120 "db_id": "thrombosis_prediction",1121 "question": "What proportion of patients who had signs of thrombocytopenia had SLE diagnosed?",1122 "evidence": "thrombocytopenia' refers to symptoms; 'SLE' refers to diagnosis; calculation = DIVIDE(SUM(DIAGNOSIS LIKE '%ITP%'), SUM(DIAGNOSIS LIKE '%SLE%')) MULTIPLY 100",1123 "SQL": "WITH thrombocytopenia_patients AS ( SELECT DISTINCT `ID` FROM `Examination` WHERE `Symptoms` LIKE '%thrombocytopenia%' ), SLE_patients AS ( SELECT DISTINCT e.`ID` FROM `Examination` e JOIN thrombocytopenia_patients t ON t.`ID` = e.`ID` WHERE e.`Diagnosis` LIKE '%SLE%' ) SELECT CAST((SELECT COUNT(*) FROM SLE_patients) AS REAL) * 100.0 / (SELECT COUNT(*) FROM thrombocytopenia_patients) AS proportion_sle_in_thrombocytopenia;",1124 "difficulty": "moderate",1125 "index": 1241126 },1127 {1128 "question_id": 1218,1129 "db_id": "thrombosis_prediction",1130 "question": "Among female patients, what percentage have uric acid (UA) levels above the normal range?",1131 "evidence": "uric acid (UA) beyond the normal range refers to UA > 8.0 and SEX = 'M' or UA > 6.5 and SEX = 'F'; female refers to Sex = 'F'",1132 "SQL": "WITH female_patients AS ( SELECT DISTINCT `ID` FROM `Patient` WHERE `SEX` = 'F' ), high_ua_females AS ( SELECT DISTINCT `ID` FROM `Laboratory` WHERE `UA` > 6.5 ) SELECT CAST(COUNT(high_ua_females.`ID`) AS REAL) * 100.0 / COUNT(female_patients.`ID`) AS percentage_high_ua FROM female_patients LEFT JOIN high_ua_females ON female_patients.`ID` = high_ua_females.`ID`;",1133 "difficulty": "moderate",1134 "index": 1251135 },1136 {1137 "question_id": 1224,1138 "db_id": "thrombosis_prediction",1139 "question": "What is the highest total bilirubin level recorded? List out the patient details with ID, sex and birthday with that index.",1140 "evidence": "the highest total bilirubin refers to MAX(T-BIL)",1141 "SQL": "SELECT T2.`T-BIL`, T1.ID, T1.SEX, T1.Birthday FROM Patient AS T1 INNER JOIN Laboratory AS T2 ON T1.ID = T2.ID ORDER BY T2.`T-BIL` DESC LIMIT 1",1142 "difficulty": "simple",1143 "index": 1261144 },1145 {1146 "question_id": 1235,1147 "db_id": "thrombosis_prediction",1148 "question": "What are the patient's diagnosis for those who has lower red blood blood cell? State their ID and age.",1149 "evidence": "patient's diagnosis refers to Diagnosis; lower red blood cell refers to RBC < 3.5; age = SUBTRACT(year(current_timestamp), year(Birthday));",1150 "SQL": "SELECT DISTINCT T1.Diagnosis, T1.ID , STRFTIME('%Y', CURRENT_TIMESTAMP) -STRFTIME('%Y', T1.Birthday) FROM Patient AS T1 INNER JOIN Laboratory AS T2 ON T1.ID = T2.ID WHERE T2.RBC < 3.5",1151 "difficulty": "moderate",1152 "index": 1271153 },1154 {1155 "question_id": 1271,1156 "db_id": "thrombosis_prediction",1157 "question": "How many patients with a normal anti-SSA came to the hospital before 2000?",1158 "evidence": "normal anti-SSA refers to SSA IN ('negative','0'); came to the hospital before 2000 refers to YEAR(`First Date`) < 2000; Should compute the number of distinct ones",1159 "SQL": "SELECT COUNT(DISTINCT p.`ID`) FROM `Patient` AS p JOIN `Laboratory` AS l ON p.`ID` = l.`ID` WHERE l.`SSA` IN ('negative','0') AND STRFTIME('%Y', p.`First Date`) < '2000';",1160 "difficulty": "moderate",1161 "index": 1281162 },1163 {1164 "question_id": 1272,1165 "db_id": "thrombosis_prediction",1166 "question": "Which patient is the first patient with an abnormal anti-SSA to come to the hospital? Please give his or her ID.",1167 "evidence": "first patient refers to ID with MIN(`First Date`); abnormal anti-SSA refers to SSA NOT IN('negative', '0');",1168 "SQL": "SELECT T1.ID FROM Patient AS T1 INNER JOIN Laboratory AS T2 ON T1.ID = T2.ID WHERE T1.`First Date` IS NOT NULL AND T2.SSA NOT IN ('negative', '0') ORDER BY T1.`First Date` ASC LIMIT 1",1169 "difficulty": "moderate",1170 "index": 1291171 },1172 {1173 "question_id": 1279,1174 "db_id": "thrombosis_prediction",1175 "question": "What is the percentage of patient who has a abnormal level of glutamic oxaloacetic transaminase level, yet he or she is diagnosed with SLE?",1176 "evidence": "abnormal level of glutamic oxaloacetic transaminase refers to GOT > = 60; percentage = MULTIPLY(DIVIDE(COUNT(ID WHERE GOT > = 60 AND Diagnosis = 'SLE'), COUNT(ID WHERE GOT > = 60)), 1.0);",1177 "SQL": "WITH abnormal AS ( SELECT DISTINCT `ID` FROM `Laboratory` WHERE `GOT` >= 60 ) SELECT 100.0 * SUM(CASE WHEN p.`Diagnosis` LIKE '%SLE%' THEN 1 ELSE 0 END) / COUNT(*) AS pct_sle_among_high_got FROM abnormal a JOIN `Patient` p ON p.`ID` = a.`ID`;",1178 "difficulty": "moderate",1179 "index": 1301180 },1181 {1182 "question_id": 1280,1183 "db_id": "thrombosis_prediction",1184 "question": "How many male patients have had at least one glutamic oxaloacetic transaminase (GOT/AST) measurement within the normal range?",1185 "evidence": "male refers to Sex = 'M'; glutamic oxaloacetic transaminase in the normal range refers to GOT < 60;",1186 "SQL": "SELECT COUNT(DISTINCT T1.ID) FROM Patient AS T1 JOIN Laboratory AS T2 ON T1.ID = T2.ID WHERE T1.SEX = 'M' AND T2.GOT < 60;",1187 "difficulty": "simple",1188 "index": 1311189 },1190 {1191 "question_id": 1286,1192 "db_id": "thrombosis_prediction",1193 "question": "For the patient with an abnormal alkaliphophatase level, how many of them are admitted to the hospital?",1194 "evidence": "abnormal alkaliphophatase level refers to ALP > = 300; admitted to the hospital refers to Admission = '+';",1195 "SQL": "SELECT COUNT(DISTINCT T1.`ID`) FROM `Patient` AS T1 JOIN `Laboratory` AS T2 ON T1.`ID` = T2.`ID` WHERE T2.`ALP` >= 300 AND T1.`Admission` = '+';",1196 "difficulty": "simple",1197 "index": 1321198 },1199 {1200 "question_id": 1302,