Database: retail_complains
Question: How many male clients born in the year 1977 were given priority 0 in their complaints?
Evidence: male refers to sex = 'Male';
SQL: SELECT COUNT(T1.sex) FROM client AS T1 INNER JOIN callcenterlogs AS T2 ON T1.client_id = T2.`rand client` WHERE T1.sex = 'Male' AND T2.priority = 0 AND T1.year = 1997

Database: books
Question: In books authored by Abraham Lincoln, what is the percentage of the books published in 1992?
Evidence: "Abraham Lincoln" is the author_name; published in 1992 refers to publication_date LIKE '1992%'; percentage = Divide (Sum(publication_date LIKE '1992%'), Count(publication_date)) * 100
SQL: SELECT CAST(SUM(CASE WHEN STRFTIME('%Y', T1.publication_date) = '1992' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) 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 WHERE T3.author_name = 'Abraham Lincoln'

Database: genes
Question: For the pairs of genes both from the class ATPases, what is the average expression correlation score?
SQL: SELECT AVG(T2.Expression_Corr) FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 WHERE T1.Class = 'ATPases'

Database: disney
Question: Indicate the release date of the film The Lion King directed by Roger Allers.
Evidence: The Lion King refers to movie_title = 'The Lion King'; Roger Allers refers to director = 'Roger Allers';
SQL: SELECT T1.release_date FROM characters AS T1 INNER JOIN director AS T2 ON T1.movie_title = T2.name WHERE T2.director = 'Roger Allers' AND T1.movie_title = 'The Lion King'

Database: authors
Question: What are the conference name and journal name of paper written by Shueh-Lin Yau? List down the name of co-authors and provide the title of that paper.
Evidence: Shueh-Lin Yau is the name of author;
SQL: SELECT T1.ConferenceId, T1.JournalId, T2.Name, T1.Title FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId INNER JOIN Conference AS T3 ON T1.ConferenceId = T3.Id INNER JOIN Journal AS T4 ON T1.JournalId = T4.Id WHERE T2.Name = 'Shueh-Lin Yau'

Database: talkingdata
Question: What is the age group of most OPPO users?
Evidence: age group refers to group; most OPPO users refers to MAX(COUNT(phone_brand = 'OPPO')); OPPO users refers to phone_brand = 'OPPO';
SQL: SELECT T.`group` FROM ( SELECT T1.`group`, COUNT(T1.`group`) AS num FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T2.phone_brand = 'OPPO' GROUP BY T1.`group` ) AS T ORDER BY T.num DESC LIMIT 1

Database: talkingdata
Question: Provide the total number of the male users that use OPPO as their phone brand.
Evidence: male refers to gender = 'Male';
SQL: SELECT COUNT(T1.device_id) FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T2.phone_brand = 'OPPO' AND T1.gender = 'M'

Database: address
Question: Calculate the average number of beneficiaries per postal point in Guam.
Evidence: DIVIDE(SUM(total_beneficiaries), COUNT(zip_code)) where name = 'Guam';
SQL: SELECT CAST(SUM(T2.total_beneficiaries) AS REAL) / COUNT(T2.zip_code) FROM state AS T1 INNER JOIN zip_data AS T2 ON T1.abbreviation = T2.state WHERE T1.name = 'Guam'

Database: ice_hockey_draft
Question: List out the name of players who have a height of 5'8".
Evidence: name of players refers to PlayerName; height of 5'8" refers to height_in_inch = '5''8"';
SQL: SELECT T2.PlayerName FROM height_info AS T1 INNER JOIN PlayerInfo AS T2 ON T1.height_id = T2.height WHERE T1.height_in_inch = '5''8"'

Database: trains
Question: Which direction do most of the trains with rectangle-shaped second cars run?
Evidence: most of the trains refers to MAX(count(id)); second cars refers to position = 2
SQL: SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 2 AND T1.shape = 'rectangle' GROUP BY T2.direction ORDER BY COUNT(T2.id) DESC LIMIT 1

Database: shipping
Question: What is the shipment ID of the heaviest shipment that Zachery Hicks transported?
Evidence: shipment ID refers to ship_id; heaviest shipment refers to Max(weight)
SQL: SELECT T1.ship_id FROM shipment AS T1 INNER JOIN driver AS T2 ON T1.driver_id = T2.driver_id WHERE T2.first_name = 'Zachery' AND T2.last_name = 'Hicks' ORDER BY T1.weight DESC LIMIT 1

Database: video_games
Question: What is the average number of games published by a publisher?
Evidence: average number = divide(count(game_id), count(publisher_id))
SQL: SELECT CAST(COUNT(T.game_id) AS REAL) / COUNT(DISTINCT T.publisher_id) FROM game_publisher AS T

Database: movielens
Question: What is the proportion of action movies directors who are called 'box office success paradox'?
Evidence: 'box office success paradox' means average revenue exceeds their quality; The proportion can be computed by [(avg_revenue > d_quality) / ()] * 100%
SQL: SELECT CAST(SUM(IIF(T2.avg_revenue > T2.d_quality, 1, 0)) AS REAL) * 100 / COUNT(T1.movieid) FROM movies2directors AS T1 INNER JOIN directors AS T2 ON T1.directorid = T2.directorid WHERE T1.genre = 'Action'

Database: cs_semester
Question: State the name of research postgraduate student among Professor Zhihua Zhou's research assistants.
Evidence: research postgraduate student refers to type = 'RPG'; research assistant refers to the student who serves for research where the abbreviation is RA;
SQL: SELECT T3.f_name, T3.l_name FROM prof AS T1 INNER JOIN RA AS T2 ON T1.prof_id = T2.prof_id INNER JOIN student AS T3 ON T2.student_id = T3.student_id WHERE T1.first_name = 'Zhihua' AND T3.type = 'RPG' AND T1.last_name = 'Zhou'

Database: food_inspection
Question: Who is the owner of the business that has a high risk violation of 103109 and described as unclean or unsanitary food contact surfaces?
Evidence: owner refers to owner_name; high risk violation of 103109 and described as unclean or unsanitary food contact surfaces refers to risk_category = 'High Risk' where violation_type_id = 103109 and description = 'Unclean or unsanitary food contact surfaces';
SQL: SELECT DISTINCT T2.owner_name FROM violations AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T1.risk_category = 'High Risk' AND T1.violation_type_id = 103109 AND T1.description = 'Unclean or unsanitary food contact surfaces'

Database: movie_3
Question: Calculate the average payment amount per customer.
Evidence: average payment refers to AVG(amount)
SQL: SELECT AVG(amount) FROM payment GROUP BY customer_id

Database: movie_3
Question: State the name of the category which has the most number of films.
Evidence: category refers to name; most number of films refers to Max(Count(film_id))
SQL: SELECT T.name FROM ( SELECT T2.name, COUNT(T1.film_id) AS num FROM film_category AS T1 INNER JOIN category AS T2 ON T1.category_id = T2.category_id GROUP BY T2.name ) AS T ORDER BY T.num DESC LIMIT 1

Database: professional_basketball
Question: What is the average BMI of an All-star player?
Evidence: average BMI refers to AVG (Multiply(Divide(weight, Multiply(height, height)), 703))
SQL: SELECT AVG(CAST(T1.weight AS REAL) / (T1.height * T1.height)) FROM players AS T1 INNER JOIN player_allstar AS T2 ON T1.playerID = T2.playerID

Database: movie_platform
Question: What is the URL to the rating on Mubi made by user 45579900 for the movie "The Vertical Ray of the Sun" that received 20 likes?
Evidence: URL refer to rating_url; 20 likes refer to critic_likes = ’20’; user 45579900 refer to user_id
SQL: SELECT T2.rating_url FROM movies AS T1 INNER JOIN ratings AS T2 ON T1.movie_id = T2.movie_id WHERE T2.user_id = 45579900 AND T1.movie_title = 'The Vertical Ray of the Sun' AND T2.critic_likes = 20