Project 1

/*1. create a database*/
create database ecommerce;
/*3.RUN SQLCOMMAND TO SEE THE STRUCTURE OF TABLE*/
use ecommerce;
desc users_data;
select*from users_data;
/*4.RUN SQLCOMMAND 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) country,count(distinct language) language from users_data;
/*6.CHECK WHETHER MALE USERS ARE HAVING MAXIMUM FOLLOWERS OR FEMALE USERS.*/
select gender,max(socialNbfollowers) Max_followers from users_data group by gender;
/*7.CALCULATE THE TOTAL USERS THOSE (a)USES PROFILE PICTURE IN THEIR PROFILE (b)USES APPLICATIONS FOR ECOMMERCE PLATFORM (c) USES ANDROID APP (d) USES IOS APP*/
select count(hasprofilepicture)from users_data where hasAnyApp='true';
select count(*) from users_data where hasAndroidApp='true';
select count(hasAndroidApp) FROM users_data WHERE hasAndroidApp='TRUE';
select count(hasIosApp) from users_data where hasIosApp='true';
/*8.CALCULATE THE TOTAL NUMBER OF BUYERS FOR EACH COUNTRY AND SORT THE RESULTS IN DESCENDIG ORDER OF TOTAL NUMBER OF BUYERS*/
select country, count(productsBought) from users_data where productsBought!=0 group by country order by count(productsBought) desc;
/*9.CALCULATE THE TOTAL NUMBER OF SALLERS FOR EACH COUNTRY AND SORT THE RESULTS IN ASCENDING ORDER OF TOTAL NUMBER OF SELLERS*/
select distinct country,count(productsSold) from users_data where productsSold!=0 group by country order by count(productsSold);
/*10.DESPLAY NAME OF THE TOP 10 COUNTRIES HAVING MAXIMUM PRODUCTS PASS RATE*/
select distinct country, max(productsPassRate) from users_data group by country order by Max(productsPassRate) desc limit 10;
/*11.CALCULATE THE NUMBER OF USERS ON AN ECOMMERCE PLATFORM FOR DIFFERENT LANGUAGE CHOICES*/
select count(language),language from users_data group by language;
/*12.CHECK THE CHOICE OF FEMALE USERS ABOUT PUTTING THE PRODUCT IN A WISHLIST OR LIKE SOCIALLY ON AN ECOMMERCE PLAFORM*/
select count(productsWished) from users_data where (gender ='F' and productsWished>0) union select count(socialProductsLiked) from users_data where(gender='F' and socialProductsLiked>0);
/*13.CHECK THE CHOICE OF MALE USERS ABOUT BEING SELLER OR BUYER*/
select count(productsBought) from users_data where(gender='M' and productsBought>0) union select count(productsSold) from users_data where (gender='M' and productsSold>0);
/*14.WHICH COUNTRY IS HAVING MAXIMUM NUMBER OF BUYERS*/
select country,count(productsBought) from users_data group by country order by count(productsBought)desc;
/*15.LIST THE NAME OF 10 COUNTRIES HAVING ZERO NUMBER OF SELLERS*/
select country,sum(productsSold) sellers from users_data group by country order by sum(productsSold) limit 10;
/*16.DISPLAY RECORD OF TOP 110 USER 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.DISPALY THE NUMBER OF FEMALE USERSOF EACH COUNTRY AT ECOMMERCE PLATFORM*/
select country,count(gender)Female 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 from users_data where gender='M' and hasAnyApp='true' group by country;
/*20.CALCULATE THE AVERAGE NUMBER OF PRODUCTS SOLD AND BAUGHT ON ECOMMERCE PLATFORM BY MALE USERS FOR EACH COUNTRY*/
select avg(productsSold) products_Sold,avg(productsBought) products_Bought from users_data where gender ='M' group by country;