[
  {
    "db_id": "app_store",
    "question": "How many of the reviews for the app \"Brit + Co\" have a comment?",
    "evidence": "Brit + Co refers to App = 'Brit + Co'; comment refers to Translated Review NOT null;",
    "SQL": "SELECT COUNT(App) FROM user_reviews WHERE App = 'Brit + Co' AND Translated_Review IS NOT NULL"
  },
  {
    "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": "Which 1,000,000,000+ intalls apps has the most no comment reviews?",
    "evidence": "no comment refers to Translated_Review = 'nan'; most no comment reviews = (MAX(COUNT(Translated_Review = 'nan')));",
    "SQL": "SELECT T1.App FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Installs = '1,000,000+' AND T2.Translated_Review = 'nan' GROUP BY T1.App ORDER BY COUNT(T2.Translated_Review) DESC LIMIT 1"
  },
  {
    "db_id": "app_store",
    "question": "What is the rating for \"Draw A Stickman\"?",
    "evidence": "Draw A Stickman refers to App = 'Draw A Stickman';",
    "SQL": "SELECT Rating FROM playstore WHERE APP = 'Draw A Stickman'"
  },
  {
    "db_id": "app_store",
    "question": "What are the top 5 installed free apps?",
    "evidence": "free app refers to price = 0; most installed app refers to MAX(Installs);",
    "SQL": "SELECT App FROM playstore WHERE Price = 0 ORDER BY CAST(REPLACE(REPLACE(Installs, ',', ''), '+', '') AS INTEGER) DESC LIMIT 5"
  },
  {
    "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 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": "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": "How many apps have rating of 5?",
    "evidence": "FALSE;",
    "SQL": "SELECT COUNT(App) FROM playstore WHERE Rating = 5"
  },
  {
    "db_id": "app_store",
    "question": "What is the number of neutral comments from all the weather apps?",
    "evidence": "neutral comments refers to Sentiment = 'Neutral'; weather app refers to Genre = 'Weather';",
    "SQL": "SELECT COUNT(T2.Sentiment) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres = 'Weather' AND T2.Sentiment = 'Neutral'"
  },
  {
    "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": "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": "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": "How much is the size of Browser 4G and how many users have a pretty positive favorability on it?",
    "evidence": "Browser 4G is the App; pretty positive favorability refers to Sentiment_Polarity score = 0.5",
    "SQL": "SELECT T1.Size, COUNT(T1.App) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Browser 4G' AND T2.Sentiment_Polarity >= 0.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": "What is the percentage of application with 4.7 rating having more positives sentiment than negative sentiment?",
    "evidence": "percentage = DIVIDE(SUBTRACT(SUM(Sentiment = 'Positive')), (SUM(Sentiment = 'Negative')), SUM(Sentiment = 'Negative')) as percentage; having more positive sentiment than negative sentiment refers to Sentiment = 'Positive'>Sentiment = 'Negative';",
    "SQL": "SELECT CAST(COUNT(CASE WHEN ( SELECT COUNT(CASE WHEN Sentiment = 'Positive' THEN 1 ELSE NULL END) - COUNT(CASE WHEN Sentiment = 'Negative' THEN 1 ELSE NULL END) FROM user_reviews GROUP BY App ) > 0 THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.Sentiment) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Rating = 4.7"
  },
  {
    "db_id": "app_store",
    "question": "What is the lowest sentiment polarity score of the Basketball Stars app for people who dislikes the app pretty much and how many downloads does it have?",
    "evidence": "lowest sentiment polarity score refers to MIN(Sentiment_Polarity); user dislike the app pretty much refers to Sentiment_Polarity<-0.5; number of downloads it has refers to installs;",
    "SQL": "SELECT MIN(T2.Sentiment_Polarity), T1.Installs FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Basketball Stars'"
  },
  {
    "db_id": "app_store",
    "question": "What is the average review number for application with 5 rating?",
    "evidence": "average review = AVG(Review); application refers to app; Rating = 5;",
    "SQL": "SELECT AVG(Reviews) FROM playstore WHERE Rating = 5"
  },
  {
    "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": "What are the apps that users pretty like this app and how many installs amount of these apps?",
    "evidence": "users pretty much likes the app refers to Sentiment_Polarity = 'Positive';",
    "SQL": "SELECT DISTINCT T1.App, T1.Installs FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T2.Sentiment_Polarity > 0"
  },
  {
    "db_id": "app_store",
    "question": "What is the average rating of Apps falling under the racing genre and what is the percentage ratio of positive sentiment reviews?",
    "evidence": "average rating = AVG(Rating); percentage = MULTIPLY(DIVIDE((SUM(Sentiment = 'Positive')), (COUNT(*)), 100));",
    "SQL": "SELECT AVG(T1.Rating), CAST(COUNT(CASE WHEN T2.Sentiment = 'Positive' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.Sentiment) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres = 'Racing'"
  },
  {
    "db_id": "app_store",
    "question": "List apps whose rating is 3.9 and state the translated review of each app.",
    "evidence": "lowest rating refers to Rating = 1;",
    "SQL": "SELECT T1.App, T2.Translated_Review FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Rating = 3.9"
  },
  {
    "db_id": "app_store",
    "question": "What is the total installs of apps with content rating of adults only 18+ and what are the translated reviews of it?",
    "evidence": "total installs = SUM(Installs);",
    "SQL": "SELECT SUM(T1.Installs), T2.Translated_Review FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.\"Content Rating\" = 'Adults only 18+'"
  },
  {
    "db_id": "app_store",
    "question": "Which Photography app has the highest total Sentiment subjectivity score?",
    "evidence": "Photography app refers to Genre = 'Photography'; highest total sentiment subjectivity score = MAX(sum(Sentiment_Subjectivity));",
    "SQL": "SELECT T1.App FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres = 'Photography' GROUP BY T1.App ORDER BY SUM(T2.Sentiment_Subjectivity) DESC LIMIT 1"
  },
  {
    "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": "Among the role playing game genre, how many are targeted to teens and what is their average sentiment polarity score?",
    "evidence": "targeted to teen refers to Content Rating = 'Teen'; average = AVG(Sentiment_Polarity);",
    "SQL": "SELECT COUNT(T1.App), AVG(T2.Sentiment_Polarity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.\"Content Rating\" = 'Teen' AND T1.Genres = 'Role Playing'"
  },
  {
    "db_id": "app_store",
    "question": "What is the average download for entertainment apps with size no more than 1.0 M?",
    "evidence": "downloads and installs are synonyms; entertainment apps refers to Category = 'ENTERTAINMENT';",
    "SQL": "SELECT AVG(CAST(REPLACE(REPLACE(Installs, ',', ''), '+', '') AS INTEGER)) FROM playstore WHERE Category = 'ENTERTAINMENT' AND Size < '1.0M'"
  },
  {
    "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"
  },
  {
    "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": "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"
  }
]