Project 1

-- Create new schema as ecommerce

create database ecommerce;

-- Import .csv file users_data into MySQL

-- (right click on ecommerce schema -> Table Data import Wizard -> Give path of the file -> Next -> choose options : Create a new table , select delete if exist -> next -> next

use ecommerce;

-- Run SQL command to see the structure of table

DESC users_data;

-- Run SQL command to select first 100 rows of the database

select * from users_data limit 100;

-- How many distinct values exist in table for field country and language

select count(distinct country),count(distinct language)from users_data;

-- Check whether male users are having maximum followers or female users

select gender,sum(socialNbFollowers) from users_data group by gender;

-- female users have maximum followers than male users

/* Calculate the total users those

a) Uses Profile Picture in their Profile

b) Uses Application for Ecommerce platform

c) Uses Android app

d) Uses ios app */

select count(hasProfilePicture) from users_data where hasProfilePicture = "True";

select count(hasAnyApp) from users_data where hasAnyApp = "True";

select count(hasAndroidApp) from users_data where hasAndroidApp = "True";

select count(hasIosApp) from users_data where hasIosApp = "True";

-- Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers.

-- (Hint: consider only those users having at least 1 product bought.)

select country,count(productsBought) from users_data where productsBought >= 1 group by country order by count(productsBought) desc;

-- Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.

-- (Hint: consider only those users having at least 1 product sold.)

select country,count(productsSold) from users_data where productsSold >= 1 group by country order by count(productsSold);

-- Display name of top 10 countries having maximum products pass rate

select country,max(productsPassRate) from users_data group by country order by max(productsPassRate) desc limit 10;

-- Calculate the number of users on an ecommerce platform for different language choices

select language, count(language) users from users_data group by language;

/* Check the choice of female users about putting the product in a wishlist or to

like socially on an ecommerce platform.

(Hint: use UNION to answer this question.)*/

select 'Productswishedby F' Description,sum(productsWished) Quantity from users_data where gender = 'F' union select 'profucts liked by F' Description,sum(socialProductsLiked) Quantity from users_data where gender = 'F';

-- Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)

select 'Products bought by M' Description, sum(productsBought) Total from users_data where gender = 'M' union select 'Products sold by M',sum(productsSold) from users_data where gender = 'M';

-- Which country is having maximum number of buyers?

select country, max(d.pb1) maximum_buyers from (select country, sum(productsBought) as pb1 from users_data group by country ) as d group by country order by maximum_buyers desc limit 1;

-- List the name of 10 countries having zero number of sellers.

select country, sum(productsSold) as products_sold from users_data group by country order by products_sold limit 10;

-- Display record of top 110 users who have used ecommerce platform recently.

select identifierHash,daysSinceLastLogin recent from users_data order by recent limit 110;

-- Calculate the number of female users those who have not logged in since last 100 days.

select count(identifierHash) total_females_lastlogin_more100 from users_data where daysSinceLastLogin > 100 and gender = 'F';

-- Display the number of female users of each country at ecommerce platform

select country,count(identifierHash) as total_female from users_data where hasAnyAPP = 'True' and gender = 'F' group by country;

-- Display the number of male users of each country at ecommerce platform

select country,count(identifierHash) as total_male from users_data where hasAnyAPP = 'True' and gender = 'M' group by country;

-- Calculate the average number of products sold and bought on ecommerce platform by male users for each country

select country, avg(productsSold), avg(productsBought) from users_data where gender = 'M' group by country;