module 1_subtask4.sql

-- Create Playstore Database 
CREATE DATABASE playstore;

-- Check if database was created
SHOW DATABASES;

-- Create playstore apps table 
CREATE TABLE apps (
 App VARCHAR(255), 
 Category VARCHAR(255), 
 Rating DECIMAL(2,1), 
 Reviews INT, 
 Size VARCHAR(255), 
 Installs INT,
 Type VARCHAR(10), 
 Price INT, 
 Content_Rating VARCHAR(30),
 Genres VARCHAR(50), 
 Last_Updated DATE, 
 Current_Ver VARCHAR(50), 
 Android_Ver VARCHAR(50)
 );

-- Verifying that table was properly created
DESCRIBE apps;


-- Loading csv file into  `apps` Table 
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/apps_cleaned_prepared.csv' 

INTO TABLE apps 

FIELDS TERMINATED BY ',' 

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(App,Category,Rating,Reviews,Size,Installs,Type,Price,Content_Rating,Genres,@Last_Updated,Current_Ver,Android_Ver)

SET Last_Updated = STR_TO_DATE(@Last_Updated, '%m/%d/%Y');

-- Create reviews table
CREATE TABLE reviews (
App VARCHAR(255), 
Translated_Review TEXT, 
 Sentiment TEXT, 
 Sentiment_Polarity FLOAT, 
 Sentiment_Subjectivity FLOAT );

-- ALLOWING MYSQL TO LOAD REVIEWS DATA BY DISABLING SQL MODE
SELECT @@sql_mode;
SET sql_mode = ' ';

-- Load the second csv file into the reviews table
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/reviews_cleaned_prepared.csv' 
INTO TABLE reviews 
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- CHECKING BOTH TABLES
SHOW tables;
 SELECT * FROM apps;
 SELECT * FROM reviews;

-- Checking to confirm if all the rows of data were completed imported
SELECT COUNT(App) FROM apps;
SELECT COUNT(App) FROM reviews;

-- SOLUTION TO QUESTIONS

-- 1. Which apps have the highest rating in the given available dataset.
SELECT App, (SELECT MAX(Rating) FROM apps) AS max_rating 
FROM apps_v2 
GROUP BY 1 
ORDER BY 2 DESC;


-- 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, Installs, Reviews 
FROM apps 
WHERE Rating = (SELECT MAX(Rating) FROM apps) 
ORDER BY Reviews DESC;

-- 3. Which app has the highest number of reviews? Also, mention the number of reviews and category of the app.
SELECT App, Category, Reviews 
FROM apps 
WHERE Reviews = (SELECT MAX(Reviews) FROM apps);

--  4. What is the total amount of revenue generated by the google play store by hosting apps? 
SELECT SUM(Installs*Price) as Total_Revenue 
FROM apps;

-- 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 Category, SUM(Installs) as total_installs 
FROM apps  
GROUP BY Category
ORDER BY total_installs DESC 
LIMIT 1;

-- 6. Which Genre has the most number of published apps?
SELECT Genres, COUNT(App) as Total_Apps 
FROM apps 
GROUP BY Genres 
ORDER BY Total_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
SELECT DISTINCT App, Installs 
FROM apps 
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 apps 
WHERE Android_Ver LIKE '%4.0.3 and up%';

-- 9. How many apps from the given data set are free? Also, provide the number of paid apps.
SELECT Type, COUNT(App) as Total_Apps 
FROM apps 
GROUP BY type;

-- 10. Which is the best dating app? (Best dating app is the one having the highest number of Reviews)
SELECT App
FROM apps 
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 Sentiment, COUNT(Translated_Review) AS Total_Reviews 
FROM reviews 
WHERE App = '10 Best Foods for You' AND Sentiment IN ('Positive','Negative')
GROUP BY Sentiment;

-- 12. Which comments of ASUS SuperNote have sentiment polarity and sentiment subjectivity both as 1?
SELECT App, Translated_Review, Sentiment_Polarity, Sentiment_Subjectivity 
FROM reviews 
WHERE App LIKE '%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 Translated_Review, App, Sentiment 
FROM reviews 
WHERE App LIKE '%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, App, Sentiment, Sentiment_Polarity, Sentiment_Subjectivity 
FROM reviews 
WHERE App LIKE '%Adobe Acrobat Reader%' AND Sentiment LIKE '%Negative%';