Project 1

/*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;

select * from users_data;

/*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)) distinctCountryCount, count(distinct(language)) distinctLanguageCount from users_data;

/* Check whether male users are having maximum followers or female users. */
select gender, sum(socialNbFollowers) from users_data group by gender order by sum(socialNbFollowers) desc;
/* females has maximum followers"*/

/*Calculate the total users those
Uses Profile Picture in their Profile
Uses Application for Ecommerce platform
Uses Android app
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) Sellers 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(type) no_of_users from users_data where type="user" group by language order by count(type) desc;

/*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 'Female Choices', 'Count'
UNION
SELECT 'product_wished',count(productsWished)
FROM users_data
WHERE gender='F' and productsWished>0
UNION
SELECT 'products_liked',count(socialProductsLiked)
FROM users_data
WHERE gender='F' and socialProductsLiked>0;

/*Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.) */
select 'male choice','count'
Union
select 'seller',count(productsSold) from users_data where gender='M' and productsSold>0
union
select 'buyer', count(productsBought) from users_data where gender='M' and productsBought>0 ;

/*Which country is having maximum number of buyers?*/

SELECT country, count(productsBought) FROM users_data GROUP BY country ORDER BY count(productsBought) DESC LIMIT 1;

/* List the name of 10 countries having zero number of sellers.*/

select country, count(productsSold) from users_data where ProductsSold=0 group by country limit 10;

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

select country, count(daysSinceLastLogin) from users_data group by country order by count(daysSinceLastLogin) limit 110;

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

select gender, daysSinceLastLogin from users_data where gender='F' and daysSinceLastLogin >=100;

/*Display the number of female users of each country at ecommerce platform.*/
select count(gender), country from users_data where gender='F' AND hasAnyApp='True' group by country;

/*Display the number of male users of each country at ecommerce platform.*/

select count(gender), country from users_data where gender='M' AND hasAnyApp='True' group by country;

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

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