Project 1

#project1
#Business model Customer to Customer (C2C) allows customers to do business with each
#other. This model is growing fast with e-commerce platforms where sellers may be
#required to pay some amount and buyer can buy it without paying anything. E-Commerce
#website brings the seller and buyer to the same platform.

#Analyzing the user's database will lead to understanding the business perspective.
#Behaviour of the users can be traced in terms of business with exploration of the user’s
#database.

## 1. Create new schema as ecommerce
CREATE DATABASE ecommerce;
use ecommerce;
select count(*) from users_data;

## 2. import.csv file users_data into MYSQL
# answer:...we import the file from challenge section (bottom click here attachment file)then open MYSQL

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

## 4. Run SQl command to select first 100 row 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 weather male users are having maximumm followers or female users
SELECT gender,SUM(socialNBFollowers)FROM users_data
GROUP BY gender;

## 8. Calculate the total number of buyers for each country and sort the result
## in ascending order of total numbers of sellers
SELECT COUNT(productsBought) FROM users_data
WHERE productsBought > 0 GROUP by country order by count(productsBought);

## 9. Calculate the total number of seller for each contry and sort the result in
## ascending order of total number of sellers
SELECT COUNT(productsSold) FROM users_data
WHERE productsSold > 0;

## 10.Display name of top 10 countries having maximumm 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
SELECT 'putting products in wishlist',
COUNT(productsWished) choices FROM users_data
WHERE productsWished > 0 AND gender = 'F'
UNION SELECT 'like socially',COUNT(socialProductsLiked) choice2 FROM users_data
WHERE socialProductsLiked > 0 AND gender = 'F';

## 13 Check the choice of male users about being seller or buyer.
# (Hint: use UNION to solve this question.)
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)
FROM
users_data group by country order by count(productsBought) desc limit 1;

## 15 List the name of 10 countries having zero number of sellers.
SELECT country FROM users_data
WHERE country= all(select productsSold=0) GROUP BY country 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
AVG(productsSold) Sold, AVG(productsBought) Bought
FROM users_data
WHERE gender = 'M' AND hasAnyApp = 'TRUE'
GROUP BY country;