[
  {
    "db_id": "app_store",
    "question": "What is the rating for \"Garden Coloring Book\"? List all of its reviews.",
    "evidence": "Golfshot Plus: Golf GPS refers to App = 'Golfshot Plus: Golf GPS'; review refers to Translated_Review;",
    "SQL": "SELECT T1.Rating, T2.Translated_Review FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Garden Coloring Book'"
  },
  {
    "db_id": "app_store",
    "question": "What percentage of no comment reviews are from \"Teen\" content rating apps?",
    "evidence": "no comment refers to Translated_Review = 'nan'; percentage = DIVIDE((SUM(Content Rating = 'Teen')), COUNT(*));",
    "SQL": "SELECT CAST(COUNT(CASE WHEN T1.`Content Rating` = 'Teen' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T1.App) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Translated_Review = 'nan'"
  },
  {
    "db_id": "app_store",
    "question": "Which apps have 5 rating? List out then application name.",
    "evidence": "application name refers to App;",
    "SQL": "SELECT DISTINCT App FROM playstore WHERE Rating = 5"
  },
  {
    "db_id": "app_store",
    "question": "What is the percentage for free application with a rating 4.5 and above have not been updated since 2018?",
    "evidence": "paid refers to Type = 'Paid'; application refers to App; Rating>4.5; Last Updated>'2018; percentage = DIVIDE(SUM(Genres = 'Mature 17+' and Rating>4.5 and\u00a0substr(\"Last Updated\",-4,4)>'2018' )), (COUNT(App)) as percent;",
    "SQL": "SELECT CAST(SUM(CASE WHEN SUBSTR('Last Updated', -4) > '2018' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(App) PER FROM playstore WHERE Type = 'Free' AND Rating >= 4.5"
  },
  {
    "db_id": "app_store",
    "question": "What is the rating of Dragon Ball Legends and how many users dislike this App?",
    "evidence": "Dragon Ball Legends is the app; users who dislikes the app refers to Sentiment_Polarity<-0.5;",
    "SQL": "SELECT T1.Rating, COUNT(T2.Sentiment_Polarity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Dragon Ball Legends' AND CAST(Sentiment_Polarity AS INTEGER) < -0.5"
  },
  {
    "db_id": "app_store",
    "question": "How many of the users hold neutral attitude on \"10 Best Foods for You\" app and what category is this app?",
    "evidence": "neutral attitude refers to Sentiment = 'Neutral';",
    "SQL": "SELECT COUNT(T2.App), T1.Category FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = '10 Best Foods for You' AND T2.Sentiment = 'Neutral'"
  },
  {
    "db_id": "app_store",
    "question": "Which apps have multiple genres and what is the total sentiment subjectivity of these apps?",
    "evidence": "multiple genres refers to COUNT(Genres>1; total sentiment subjectivity = Sum(Sentiment_Subjectivity);",
    "SQL": "SELECT SUM(T2.Sentiment_Subjectivity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres > 1"
  },
  {
    "db_id": "app_store",
    "question": "What are the content ratings for the apps that have \"gr8\" in their comments?",
    "evidence": "app with gr8 in their comments refers to Translated_Review LIKE '%gr8%';",
    "SQL": "SELECT DISTINCT T1.`Content Rating` FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Translated_Review LIKE '%gr8%'"
  },
  {
    "db_id": "app_store",
    "question": "Which of the app is the best selling app and what is the sentiments polarity of it?",
    "evidence": "best selling app = MAX(MULTIPLY(Price, Installs));",
    "SQL": "SELECT T1.App, T2.Sentiment_Polarity FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App ORDER BY T1.Price * CAST(REPLACE(REPLACE(Installs, ',', ''), '+', '') AS INTEGER) DESC LIMIT 1"
  },
  {
    "db_id": "app_store",
    "question": "List the top 5 shopping apps with the most reviews.",
    "evidence": "shopping apps refers to Genre = 'Shopping'; most reviews refers to MAX(Reviews);",
    "SQL": "SELECT DISTINCT App FROM playstore WHERE Genres = 'Shopping' GROUP BY App ORDER BY COUNT(App) DESC LIMIT 5"
  },
  {
    "db_id": "app_store",
    "question": "What is the average sentiment polarity score of the Cooking Fever app? Indicate the age group that the app is targeted at.",
    "evidence": "average sentiment polarity score = AVG(Sentiment_Polarity); age group the app is target at refers to Content Rating;",
    "SQL": "SELECT AVG(T2.Sentiment_Polarity), T1.\"Content Rating\" FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Cooking Fever'"
  },
  {
    "db_id": "app_store",
    "question": "List the top 5 lowest rated puzzle games and count the number of negative sentiments the games received.",
    "evidence": "lowest rating refers to MIN(Rating); puzzle is the genre;",
    "SQL": "SELECT T1.App, COUNT(T1.App) COUNTNUMBER FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Sentiment = 'Negative' GROUP BY T1.App ORDER BY T1.Rating LIMIT 5"
  },
  {
    "db_id": "app_store",
    "question": "For the Honkai Impact 3rd App, what is the highest sentiment polarity score and what genre does it belong to?",
    "evidence": "highest sentiment polarity score refers to MAX(Sentiment_Polarity);",
    "SQL": "SELECT MAX(T2.Sentiment_Polarity), T1.Genres FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Honkai Impact 3rd' AND T2.Sentiment_Polarity > 0.5 GROUP BY T1.Genres"
  },
  {
    "db_id": "app_store",
    "question": "List out genre that have downloads more than 1000000000.",
    "evidence": "downloads and installs are synonyms; Installs = '1,000,000,000+';",
    "SQL": "SELECT Genres FROM playstore WHERE Installs = '1,000,000,000+' GROUP BY Genres"
  },
  {
    "db_id": "app_store",
    "question": "What is the name and category of the app with the highest amount of -1 sentiment polarity score?",
    "evidence": "highest amount of -1 sentiment polarity score refers to MAX(Count(Sentiment_Polarity = 1.0))",
    "SQL": "SELECT DISTINCT T1.App, T1.Category FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Sentiment_Polarity = '-1.0'"
  },
  {
    "db_id": "app_store",
    "question": "Name the top 10 most reviewed apps.",
    "evidence": "most reviewed app refers to MAX(Reviews);",
    "SQL": "SELECT DISTINCT App FROM playstore ORDER BY Reviews DESC LIMIT 10"
  },
  {
    "db_id": "app_store",
    "question": "How many apps that are only compatible with Android ver 8.0 and above? List down the users' sentiment of these apps.",
    "evidence": "compatible with android refers to Android Ver; Android Ver\" = '8.0 and up';",
    "SQL": "SELECT DISTINCT Sentiment FROM user_reviews WHERE App IN ( SELECT App FROM playstore WHERE `Android Ver` = '8.0 and up' )"
  },
  {
    "db_id": "app_store",
    "question": "What is the rating and the total Sentiment subjectivity score of \"Onefootball - Soccer Scores\"?",
    "evidence": "Onefootball - Soccer Scores refers to App = 'Onefootball - Soccer Scores';",
    "SQL": "SELECT T1.Rating, SUM(T2.Sentiment_Subjectivity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Onefootball - Soccer Scores'"
  },
  {
    "db_id": "app_store",
    "question": "How many neutral reviews does the app \"Dino War: Rise of Beasts\" have?",
    "evidence": "neutral reviews refers to Sentiment = 'Neutral';",
    "SQL": "SELECT COUNT(App) FROM user_reviews WHERE App = 'Dino War: Rise of Beasts' AND Sentiment = 'Neutral'"
  },
  {
    "db_id": "app_store",
    "question": "List out the top 3 genre for application with a sentiment review greater than 0.5.",
    "evidence": "sentiment review refers to Sentiment_Polarity; Sentiment_Polarity>0.5;",
    "SQL": "SELECT Genres FROM playstore WHERE App IN ( SELECT App FROM user_reviews WHERE Sentiment = 'Positive' AND Sentiment_Polarity > 0.5 ORDER BY Sentiment_Polarity DESC LIMIT 3 )"
  },
  {
    "db_id": "app_store",
    "question": "What is the percentage ratio between positive sentiments and negative sentiments that are in Fate/Grand Order? Also indicate the current version.",
    "evidence": "Fate/Grand Order is the App; percentage ratio = MULTIPLY(DIVIDE((SUM(Sentiment = 'Positive')), (SUM(Sentiment = 'Negative'))), 100);",
    "SQL": "SELECT CAST(SUM(CASE WHEN T2.Sentiment = 'Positive' THEN 1 ELSE 0 END) AS REAL) * 100 / SUM(CASE WHEN T2.Sentiment = 'Negative' THEN 1 ELSE 0 END), T1.`Current Ver` FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Fate/Grand Order (English)' AND T1.`Current Ver` = '1.18.0'"
  },
  {
    "db_id": "app_store",
    "question": "What is the average price for a dating application?",
    "evidence": "average price = AVG(Price where Genre = 'Dating'); dating application refers to Genre = 'Dating';",
    "SQL": "SELECT AVG(Price) FROM playstore WHERE Genres = 'Dating'"
  },
  {
    "db_id": "app_store",
    "question": "What is the total Sentiment polarity score of the most expensive app?",
    "evidence": "total sentiment polarity score = sum(Sentiment_Polarity); most expensive app refers to MAX(Price);",
    "SQL": "SELECT SUM(T2.Sentiment_Polarity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Price = ( SELECT MAX(Price) FROM playstore )"
  },
  {
    "db_id": "app_store",
    "question": "What are the apps with only 5,000+ installs?",
    "evidence": "Installs = '5,000+';",
    "SQL": "SELECT DISTINCT App FROM playstore WHERE Installs = '5,000+'"
  },
  {
    "db_id": "app_store",
    "question": "Which free app has the most Negative comments?",
    "evidence": "paid app refers to Type = 'Paid'; negative comment refers to Sentiment = 'Negative'; paid app with most negative comments refers to MAX(COUNT(Sentiment = 'Negative')) where Type = 'Paid';",
    "SQL": "SELECT T1.App FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Type = 'Free' AND T2.Sentiment = 'Negative' GROUP BY T1.App ORDER BY COUNT(T2.Sentiment) DESC LIMIT 1"
  },
  {
    "db_id": "app_store",
    "question": "How many apps have rating of 5?",
    "evidence": "FALSE;",
    "SQL": "SELECT COUNT(App) FROM playstore WHERE Rating = 5"
  },
  {
    "db_id": "app_store",
    "question": "List all the negative comments on the \"Dog Run - Pet Dog Simulator\" app.",
    "evidence": "negative comment refers to Sentiment = 'Negative';",
    "SQL": "SELECT Translated_Review FROM user_reviews WHERE App = 'Dog Run - Pet Dog Simulator' AND Sentiment = 'Negative'"
  },
  {
    "db_id": "app_store",
    "question": "How much is the average sentiment polarity score of Golf GPS Rangefinder: Golf Pad and what is it's rating in the Google Play Store?",
    "evidence": "average sentiment polarity score = AVG(Sentiment_Polarity); Golf GPS Rangefinder: Golf Pad\u00a0 is the App;",
    "SQL": "SELECT AVG(T2.Sentiment_Polarity), T1.Rating FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Golf GPS Rangefinder: Golf Pad'"
  },
  {
    "db_id": "app_store",
    "question": "List down application that have not been updated since 2015. What is the percentage of this application having more negative sentiment than positive sentiment?",
    "evidence": "percentage = DIVIDE(SUBTRACT(SUM(Sentiment = 'Positive')), (SUM(Sentiment = 'Negative'))), (SUM(Sentiment = 'Negative')) as percent; Last Updated>'2015';",
    "SQL": "SELECT CAST((( SELECT COUNT(*) Po FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE SUBSTR(T1.\"Last Updated\", -4, 4) > '2015' AND T2.Sentiment = 'Positive' ) - ( SELECT COUNT(*) Ne FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE SUBSTR(T1.\"Last Updated\", -4, 4) > '2015' AND T2.Sentiment = 'Negative' )) AS REAL) * 100 / ( SELECT COUNT(*) NUM FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE SUBSTR(T1.\"Last Updated\", -4, 4) > '2015' )"
  },
  {
    "db_id": "app_store",
    "question": "For the Akinator app, how many reviews have sentiment subjectivity of no more than 0.5 and what is its current version?",
    "evidence": "Sentiment_Subjectivity<0.5; current version refers to Current Ver;",
    "SQL": "SELECT COUNT(T2.Sentiment_Subjectivity), T1.\"Current Ver\" FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Akinator' AND T2.Sentiment_Subjectivity < 0.5"
  }
]