Playstore queries.sql

CREATE DATABASE CSSolver;
USE CSSolver;

-- 1.1. Which apps have the highest rating in the given available dataset?
SELECT App,Rating
FROM cssolver.`cleaned _data1`
ORDER BY Rating DESC LIMIT 1;

-- 2. What are the number of installs and reviews for the above apps? Return the apps with the highest reviews to the top.--
SELECT App, COUNT(Installs),COUNT(Reviews)
FROM cssolver.`cleaned _data1`  
ORDER BY App DESC LIMIT 1;

-- 3.Which app has the highest number of reviews? Also, mention the number of reviews and category of the app --
SELECT App,Reviews,Category
FROM cssolver.`cleaned _data1`
GROUP BY App  
ORDER BY Reviews DESC LIMIT 1;

-- 4.What is the total amount of revenue generated by the google play store by hosting apps? (Whenever a user buys apps from the
-- google play store, the amount is considered in the revenue) --
SELECT SUM(Price) AS TOTAL_REVENUE FROM cssolver.`cleaned _data1`;

-- 5.Which Category of google play store apps has the highest number of installs?
-- also, find out the total number of installs for that particular category.--

SELECT App,Category,COUNT(Installs) as Number_OF_INSTALLS
FROM cssolver.`cleaned _data1`
GROUP BY Category  
ORDER BY Installs DESC LIMIT 10;

-- 6. Which Genre has the most number of published apps? --
SELECT  Genres ,COUNT(*) AS Most_Number_of_puplished_Apps
FROM cssolver.`cleaned _data1`
GROUP BY Genres
ORDER BY Most_Number_of_puplished_Apps DESC LIMIT 1; 

--  7.Provide the list of all games ordered in such a way that the game
-- that has the highest number of installs is displayed on the top
-- (to avoid duplicate results use distinct)--
 SELECT DISTINCT App, Installs
FROM cssolver.`cleaned _data1`
WHERE Category = 'GAME'
ORDER BY Installs DESC;


-- 8.Provide the list of apps that can work on android version 4.0.3 and UP.--
SELECT App,`Android Ver`
FROM cssolver.`cleaned _data1` 
WHERE `Android Ver` LIKE '4.0.3%';

-- 9.How many apps from the given data set are free? Also, provide the number of paid apps.

SELECT COUNT(*)
FROM cssolver.`cleaned _data1`  
 WHERE Type = 'Free' ;

SELECT COUNT(*)
FROM cssolver.`cleaned _data1`
 WHERE Type = 'Paid' ;

-- 10.Which is the best dating app? (Best dating app is the one having the highest number of Reviews) --
SELECT App,Reviews
FROM cssolver.`cleaned _data1`
WHERE Category = 'DATING'
ORDER BY Reviews DESC LIMIT 1;

-- 11.Get the number of reviews having positive sentiment and number of reviews having negative sentiment 
-- for the app 10 best foods for you and compare them.--

SELECT
    SUM(CASE WHEN Sentiment = 'Positive' THEN 1 ELSE 0 END) AS Positive_reviews,
    SUM(CASE WHEN Sentiment = 'Negative' THEN 1 ELSE 0 END) AS Negative_reviews
FROM cssolver.`playstoreapp_reviews`
WHERE App = '10 best foods for you';



-- 12.Which comments of ASUS SuperNote have sentiment polarity and sentiment subjectivity both as 1? --

SELECT Translated_Review
FROM cssolver.`playstoreapp_reviews`
WHERE App = 'ASUS SuperNote' AND Sentiment_polarity = 1 AND Sentiment_Subjectivity = 1;


-- 13.Get all the neutral sentiment reviews for the app Abs Training-Burn belly fat --
SELECT *
FROM cssolver.`playstoreapp_reviews` 
WHERE App = 'Abs Training-Burn belly fat' AND sentiment = 'neutral';


-- 14.Extract all negative sentiment reviews for Adobe Acrobat Reader with their sentiment polarity and sentiment subjectivity --
SELECT Translated_Review, Sentiment_Polarity, Sentiment_Subjectivity
FROM cssolver.`playstoreapp_reviews` 
WHERE App = 'Adobe Acrobat Reader' AND Sentiment = 'negative';