Project
1.CREATE SCHEMA ecommerce;
2.Import .csv file users_data into MySQL-
- Go to navigator unders, schemas select ecommerce database ,then right click on ecommerce1 and go for table data import wizard.
- SELECT the file path for users_data.csv then click next.
- Select the existing table type ecommerce.users_data and click next.
- In configure import setting check the column and encodind after that click next
- In import data click next, data importation process starts
SELECT * FROM table1;
3.describe users_data;
4.select *from users_data limit 100;
5.Country 200 and language 5 have distinct values exist.
Query-SELECT COUNT(DISTINCT country),COUNT(DISTINCT language) FROM users_data;
6.Female users having maximum followers.
Query-select gender,sum(socialNBFollowers) As Maximumfollowers
from users_data
group by gender;
7.
a.Uses Profile Picture in their Profile-
Total users-97018.
Query-SELECT COUNT(*) "Uses Profile Picture"FROM users_data WHERE hasProfilePicture="TRUE";
b.Uses Application for Ecommerce platform-
Total users-26174.
Query-SELECT COUNT(*) "ANY APP users" FROM users_data WHERE hasAnyApp="TRUE";
c.Uses Android app-
Total users-4819.
Query-SELECT COUNT(*) "ANDROID USERS" FROM users_data WHERE hasAndroidApp="TRUE";
d.Uses ios app-
Total users-21527.
Query-SELECT COUNT(*) "IOS USERS" FROM users_data WHERE hasIosApp="TRUE";
8. SELECT
country,
SUM(productsBought) 'total number of buyers for each country'
FROM
users_data
GROUP BY country
ORDER BY SUM(productsBought) DESC;
9. SELECT
country,
SUM(productssold) 'total number of sellers for each country'
FROM
users_data
GROUP BY country
ORDER BY SUM(productssold) asc;
10.SELECT country,max(productsPassRate) "maximum products pass rate" FROM users_data GROUP BY country ORDER BY MAX(productsPassRate) DESC limit 10 ;
11.SELECT language,COUNT(*) "number of users on an ecommerce platform" FROM users_data GROUP BY language;
12.SELECT SUM(productsWished) "product in a wishlist",sum(socialProductsLiked) " like socially on an ecommerce platform" FROM users_data WHERE gender="F" ;
13.SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender="M";
14.SELECT country, SUM(productsBought) FROM users_data GROUP BY country ORDER BY SUM(productsBought) DESC LIMIT 1;
15.SELECT country,productsSold FROM users_data GROUP BY country HAVING SUM(productsSold)=0 LIMIT 10;
16.SELECT identifierHash,daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin LIMIT 110;
17.SELECT gender,identifierHash,daysSinceLastLogin FROM users_data WHERE gender="F" AND daysSinceLastLogin>100 ORDER BY daysSinceLastLogin;
18.SELECT country,COUNT(gender) " number of female users" FROM users_data WHERE gender="F" GROUP BY country ORDER BY COUNT(gender) DESC;
19.SELECT country,COUNT(gender) " number of male users" FROM users_data WHERE gender="M" GROUP BY country ORDER BY COUNT(gender) DESC;
20.SELECT country,AVG(productsSold) "AVERAGE OF PRODUCT SOLD",AVG(productsBought) "AVERAGE OF PRODUCT BOUGHT" FROM users_data WHERE gender="M" GROUP BY country ORDER BY "AVERAGE OF PRODUCT SOLD";