 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **museum** | Museum_ID | + |   | 
 | 2 |   | Name |   |   | 
 | 3 |   | Num_of_Staff |   |   | 
 | 4 |   | Open_Year |   |   | 
 | 5 | **visitor** | ID | + |   | 
 | 6 |   | Name |   |   | 
 | 7 |   | Level_of_membership |   |   | 
 | 8 |   | Age |   |   | 
 | 9 | **visit** | Museum_ID | + | --> 1 | 
 | 10 |   | visitor_ID |   | --> 5 | 
 | 11 |   | Num_of_Ticket |   |   | 
 | 12 |   | Total_spent |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_411 | How many visitors below age 30 are there? | SELECT count(*) FROM visitor WHERE age  <  30 | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​30:​col:​visitor:​Age] <br>4. AGGREGATE[count, #3] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Age, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<:​30:​col:​visitor:​Age] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_412 | Find the names of the visitors whose membership level is higher than 4, and order the results by the level from high to low. | SELECT name FROM visitor WHERE Level_of_membership  >  4 ORDER BY Level_of_membership DESC | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Level_of_membership, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​4:​col:​visitor:​Level_of_membership] <br>4. PROJECT[col:​visitor:​Name, #3] <br>5. PROJECT[col:​visitor:​Level_of_membership, #3] <br>6. SORT[#4, #5, sortdir:​descending] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Level_of_membership, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​4:​col:​visitor:​ID] <br>4. PROJECT[col:​visitor:​Name, #3] <br>5. PROJECT[col:​visitor:​Level_of_membership, #3] <br>6. SORT[#4, #5, sortdir:​descending] <br> | - | medium | 
  | SPIDER_dev_413 | What is the average age of the visitors whose membership level is not higher than 4? | SELECT avg(age) FROM visitor WHERE Level_of_membership  <=  4 | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Level_of_membership, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​4:​col:​visitor:​Level_of_membership] <br>4. PROJECT[col:​visitor:​Age, #3] <br>5. AGGREGATE[avg, #4] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​ID, #1] <br>3. COMPARATIVE[#1, #2, comparative:​<=:​4:​col:​visitor:​ID] <br>4. PROJECT[col:​visitor:​Age, #3] <br>5. AGGREGATE[avg, #4] <br> | - | easy | 
  | SPIDER_dev_414 | Find the name and membership level of the visitors whose membership level is higher than 4, and sort by their age from old to young. | SELECT name ,  Level_of_membership FROM visitor WHERE Level_of_membership  >  4 ORDER BY age DESC | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Level_of_membership, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​4:​col:​visitor:​Level_of_membership] <br>4. PROJECT[col:​visitor:​Name, #3] <br>5. PROJECT[col:​visitor:​Level_of_membership, #3] <br>6. PROJECT[col:​visitor:​Age, #3] <br>7. UNION[#4, #5] <br>8. SORT[#7, #6, sortdir:​descending] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​4:​col:​visitor:​ID] <br>4. PROJECT[col:​visitor:​Name, #3] <br>5. PROJECT[col:​visitor:​Level_of_membership, #3] <br>6. PROJECT[col:​visitor:​Age, #3] <br>7. UNION[#4, #5] <br>8. SORT[#7, #6, sortdir:​descending] <br> | - | medium | 
  | SPIDER_dev_415 | Find the id and name of the museum that has the most staff members? | SELECT museum_id ,  name FROM museum ORDER BY num_of_staff DESC LIMIT 1 | 1. SELECT[tbl:​museum] <br>2. PROJECT[col:​museum:​Num_of_Staff, #1] <br>3. GROUP[col:​museum:​Num_of_Staff, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​museum:​Name, #4] <br>6. PROJECT[col:​museum:​Museum_ID, #4] <br>7. UNION[#6, #5] <br> | 1. SELECT[tbl:​museum] <br>2. PROJECT[col:​museum:​Num_of_Staff, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​museum:​Museum_ID, #4] <br>6. PROJECT[col:​museum:​Name, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_416 | Find the average number of staff working for the museums that were open before 2009. | SELECT avg(num_of_staff) FROM museum WHERE open_year  <  2009 | 1. SELECT[tbl:​museum] <br>2. FILTER[#1, comparative:​<:​2009:​col:​museum:​Open_Year] <br>3. PROJECT[col:​museum:​Num_of_Staff, #2] <br>4. AGGREGATE[avg, #3] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​<:​2009:​col:​museum:​Open_Year] <br>3. PROJECT[col:​museum:​Num_of_Staff, #2] <br>4. GROUP[sum, #3, #2] <br>5. AGGREGATE[avg, #4] <br> | + | easy | 
  | SPIDER_dev_417 | What are the opening year and staff number of the museum named Plaza Museum? | SELECT Open_Year ,  Num_of_Staff FROM museum WHERE name  =  'Plaza Museum' | 1. SELECT[tbl:​museum] <br>2. FILTER[#1, comparative:​=:​Plaza Museum:​col:​museum:​Name] <br>3. PROJECT[col:​museum:​Open_Year, #2] <br>4. PROJECT[col:​museum:​Num_of_Staff, #2] <br>5. AGGREGATE[col:​museum:​Num_of_Staff, #4] <br>6. UNION[#3, #5] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Plaza Museum:​col:​museum:​Name] <br>3. PROJECT[col:​museum:​Open_Year, #2] <br>4. PROJECT[col:​museum:​Num_of_Staff, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_418 | find the names of museums which have more staff than the minimum staff number of all museums opened after 2010. | SELECT name FROM museum WHERE num_of_staff  >  (SELECT min(num_of_staff) FROM museum WHERE open_year  >  2010) | 1. SELECT[tbl:​museum] <br>2. FILTER[#1, comparative:​>:​2010:​col:​museum:​Open_Year] <br>3. PROJECT[col:​museum:​Num_of_Staff, #2] <br>4. GROUP[col:​museum:​Num_of_Staff, #3, #2] <br>5. AGGREGATE[min, #4] <br>6. PROJECT[col:​museum:​Num_of_Staff, #1] <br>7. GROUP[col:​museum:​Num_of_Staff, #6, #1] <br>8. COMPARATIVE[#1, #7, comparative:​>:​#5] <br>9. PROJECT[col:​museum:​Name, #8] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​>:​2010:​col:​museum:​Open_Year] <br>3. PROJECT[col:​museum:​Num_of_Staff, #2] <br>4. AGGREGATE[min, #3] <br>5. PROJECT[col:​museum:​Num_of_Staff, #1] <br>6. COMPARATIVE[#1, #5, comparative:​>:​#4] <br>7. PROJECT[col:​museum:​Name, #6] <br> | + | hard | 
  | SPIDER_dev_419 | find the id, name and age for visitors who visited some museums more than once. | SELECT t1.id ,  t1.name ,  t1.age FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id GROUP BY t1.id HAVING count(*)  >  1 | 1. SELECT[col:​visit:​visitor_ID] <br>2. PROJECT[tbl:​visitor, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​1] <br>5. PROJECT[col:​visitor:​ID, #4] <br>6. PROJECT[col:​visitor:​Name, #4] <br>7. PROJECT[col:​visitor:​Age, #4] <br>8. UNION[#5, #6, #7] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​>:​RiverPark Museum:​col:​museum:​Name] <br>3. PROJECT[col:​museum:​Museum_ID, #2] <br>4. PROJECT[col:​museum:​Name, #2] <br>5. PROJECT[col:​museum:​Name, #2] <br>6. UNION[#3, #4, #5] <br> | - | medium | 
  | SPIDER_dev_420 | What are the id, name and membership level of visitors who have spent the largest amount of money in total in all museum tickets? | SELECT t2.visitor_id ,  t1.name ,  t1.Level_of_membership FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id GROUP BY t2.visitor_id ORDER BY sum(t2.Total_spent) DESC LIMIT 1 | 1. SELECT[tbl:​visitor] <br>2. PROJECT[museum tickets of #REF, #1] <br>3. PROJECT[col:​visit:​Total_spent, #2] <br>4. GROUP[sum, #3, #1] <br>5. COMPARATIVE[#1, #4, comparative:​max:​None] <br>6. PROJECT[col:​visit:​visitor_ID, #5] <br>7. PROJECT[col:​visitor:​Name, #5] <br>8. PROJECT[col:​visitor:​Level_of_membership, #5] <br>9. UNION[#6, #7, #8] <br> | 1. SELECT[tbl:​museum] <br>2. PROJECT[col:​visit:​Total_spent, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​museum:​Museum_ID, #4] <br>6. PROJECT[col:​museum:​Name, #4] <br>7. PROJECT[col:​museum:​Name, #4] <br>8. UNION[#5, #6, #7] <br> | - | extra | 
  | SPIDER_dev_421 | What are the id and name of the museum visited most times? | SELECT t2.Museum_ID ,  t1.name FROM museum AS t1 JOIN visit AS t2 ON t1.Museum_ID  =  t2.Museum_ID GROUP BY t2.Museum_ID ORDER BY count(*) DESC LIMIT 1 | 1. SELECT[tbl:​museum] <br>2. PROJECT[tbl:​visit, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​visit:​Museum_ID, #4] <br>6. PROJECT[col:​museum:​Name, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​museum] <br>2. PROJECT[tbl:​visit, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​museum:​Museum_ID, #4] <br>6. PROJECT[col:​museum:​Name, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_423 | Find the name and age of the visitor who bought the most tickets at once. | SELECT t1.name ,  t1.age FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id ORDER BY t2.num_of_ticket DESC LIMIT 1 | 1. SELECT[tbl:​visitor] <br>2. PROJECT[tbl:​visitor, #1] <br>3. PROJECT[col:​visit:​Num_of_Ticket, #2] <br>4. GROUP[col:​visit:​Num_of_Ticket, #3, #2] <br>5. SUPERLATIVE[comparative:​max:​None, #1, #4] <br>6. PROJECT[col:​visitor:​Name, #5] <br>7. PROJECT[col:​visitor:​Age, #5] <br>8. UNION[#6, #7] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visit:​Num_of_Ticket, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​visitor:​Name, #4] <br>6. PROJECT[col:​visitor:​Age, #4] <br>7. UNION[#5, #6] <br> | + | hard | 
  | SPIDER_dev_424 | What are the average and maximum number of tickets bought in all visits? | SELECT avg(num_of_ticket) ,  max(num_of_ticket) FROM visit | 1. SELECT[tbl:​visit] <br>2. PROJECT[col:​visit:​Num_of_Ticket, #1] <br>3. AGGREGATE[col:​visit:​Num_of_Ticket, #2] <br>4. AGGREGATE[avg, #3] <br>5. AGGREGATE[max, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​visit] <br>2. PROJECT[col:​visit:​Num_of_Ticket, #1] <br>3. GROUP[sum, #2, #1] <br>4. AGGREGATE[avg, #3] <br>5. AGGREGATE[max, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_425 | What is the total ticket expense of the visitors whose membership level is 1? | SELECT sum(t2.Total_spent) FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id WHERE t1.Level_of_membership  =  1 | 1. SELECT[tbl:​visitor] <br>2. FILTER[#1, comparative:​=:​1:​col:​visitor:​Level_of_membership] <br>3. PROJECT[col:​visit:​Total_spent, #2] <br>4. AGGREGATE[sum, #3] <br> | 1. SELECT[tbl:​visitor] <br>2. PROJECT[col:​visitor:​Level_of_membership, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​1:​col:​visitor:​Level_of_membership] <br>4. PROJECT[col:​visit:​Total_spent, #3] <br>5. AGGREGATE[sum, #4] <br> | + | medium | 
  | SPIDER_dev_426 | What is the name of the visitor who visited both a museum opened before 2009 and a museum opened after 2011? | SELECT t1.name FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id JOIN museum AS t3 ON t3.Museum_ID  =  t2.Museum_ID WHERE t3.open_year  <  2009 INTERSECT SELECT t1.name FROM visitor AS t1 JOIN visit AS t2 ON t1.id  =  t2.visitor_id JOIN museum AS t3 ON t3.Museum_ID  =  t2.Museum_ID WHERE t3.open_year  >  2011 | 1. SELECT[tbl:​museum] <br>2. FILTER[#1, comparative:​>:​2011:​col:​museum:​Open_Year] <br>3. FILTER[#1, comparative:​<:​2009:​col:​museum:​Open_Year] <br>4. PROJECT[tbl:​visitor, #1] <br>5. INTERSECTION[#4, #2, #3] <br>6. PROJECT[col:​visitor:​Name, #5] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​>:​2011:​col:​museum:​Open_Year] <br>3. COMPARATIVE[#1, #1, comparative:​<:​2009:​col:​museum:​Open_Year] <br>4. INTERSECTION[#1, #2, #3] <br>5. PROJECT[col:​museum:​Name, #4] <br> | - | extra | 
  | SPIDER_dev_427 | Find the number of visitors who did not visit any museum opened after 2010. | SELECT count(*) FROM visitor WHERE id NOT IN (SELECT t2.visitor_id FROM museum AS t1 JOIN visit AS t2 ON t1.Museum_ID  =  t2.Museum_ID WHERE t1.open_year  >  2010) | 1. SELECT[tbl:​visitor] <br>2. PROJECT[tbl:​museum, #1] <br>3. COMPARATIVE[#1, #2, comparative:​>:​2010:​col:​museum:​Open_Year] <br>4. DISCARD[#1, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​>:​2010:​col:​museum:​Open_Year] <br>3. PROJECT[tbl:​visit, #2] <br>4. SELECT[tbl:​visit] <br>5. DISCARD[#4, #3] <br>6. AGGREGATE[count, #5] <br> | - | extra | 
  | SPIDER_dev_428 | How many museums were opened after 2013 or before 2008? | SELECT count(*) FROM museum WHERE open_year  >  2013 OR open_year  <  2008 | 1. SELECT[tbl:​museum] <br>2. FILTER[#1, comparative:​>:​2013:​col:​museum:​Open_Year] <br>3. FILTER[#1, comparative:​<:​2008:​col:​museum:​Open_Year] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | 1. SELECT[tbl:​museum] <br>2. COMPARATIVE[#1, #1, comparative:​>:​2008:​col:​museum:​Open_Year] <br>3. COMPARATIVE[#1, #1, comparative:​<:​2008:​col:​museum:​Open_Year] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | - | medium | 
 ***
 Exec acc: **0.5294**
