Project 1

##1. Create new schema as ecommerce
create database ecommerce;
use ecommerce;

## 2.Import .csv file users_data into MySQL
---- We import the file in navigator by using the option load spatial data of ecommerce options

## 3.Run SQL command to see the structure of table
desc users_data;

## 4.Run SQL command to select first 100 rows of the database
select * from users_data limit 100;

## 5.How many distinct values exist in table for field country and language
select count(distinct country),count(distinct language) from users_data;

## 6.Check whether male users are having maximum followers or female users.
select gender,sum(socialNbFollowers) from users_data group by gender;

## 7.Calculate the total users those
--- a)Uses Profile Picture in their Profile,b)Uses Application for Ecommerce platform,c)Uses Android app,Uses ios app
select * from users_data;

select count(hasProfilePicture) total_users from users_data where hasProfilePicture='True'
union
select count(hasIosApp) ios_App from users_data where hasIosApp="True"
union
select count(hasAndroidApp) AndroidApp from users_data where hasAndroidApp="True"
union
select count(hasAnyApp) 'Application for Eommerce Platform' from users_data where hasAnyApp='True';

##8.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 count(ProductsBought) from users_data where ProductsBought>0 group by country order by count(ProductsBought)desc;

## 9.Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.
select count(ProductsSold) from users_data where ProductsSold>0 group by country order by count(ProductsSold)Asc;

## 10.Display name of top 10 countries having maximum products pass rate.
select distinct country,ProductsPassRate from users_data order by ProductsPassRate desc limit 10;

## 11.Calculate the number of users on an ecommerce platform for different language choices.
select count(language) from users_data;

##12.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 'putting_product_wishlist',count(ProductsWished)choices from users_data where ProductsWished >0 and gender = 'F'
union
select 'like_socially', count(socialProductsLiked)choices2 from users_data where socialProductsLiked >0 and gender='F';

## 13.Check the choice of male users about being seller or buyer.
select 'selling',count(productssold)choices
from users_data where productssold >0 and gender='M'
union
select 'buying',count(productsBought) choices2
from users_data where productsBought >0 and gender='M';

##14.Which country is having maximum number of buyers?
select country, count(ProductsBought) max_buyers from users_data group by country limit 1;

## 15.List the name of 10 countries having zero number of sellers.
select country, sum(ProductsSold) sum_sold from users_data group by country having sum(ProductsSold)=0 limit 10;

## 16.Display record of top 110 users who have used ecommerce platform recently.
select * from users_data order by daysSinceLastLogin limit 110;

##17.Calculate the number of female users those who have not logged in since last 100 days.
select count(gender) female from users_data where gender="F" and daysSinceLastLogin=100;

##18.Display the number of female users of each country at ecommerce platform.
select country, count(gender) female_users from users_data where gender='F' and hasAnyApp='true' group by country;

##19.Display the number of male users of each country at ecommerce platform.
select country, count(gender) male_users from users_data where gender='M' and hasAnyApp='true' group by country;

## 20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
select country, avg(ProductsSold) average_products_sold,avg(ProductsBought) avg_products_bought from users_data where gender='M' and hasAnyApp='true' group by country;