Project 1

1) Create new schema as ecommerce

Ans)

CREATE DATABASE ecommerce;

2) Import .csv file users_data into MySQL

Ans) Right click on ecommerce from Navigator window and follow instructions

3) Run SQL command to see the structure of table

Ans)

USE ecommerce;
DESCRIBE users_data ;

4) Run SQL command to select first 100 rows of the database

Ans)

SELECT
*
FROM
users_data
LIMIT 100;

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

ANs)

SELECT
COUNT(DISTINCT country) CountryList,
COUNT(DISTINCT language) LanguageList
FROM
users_data;

6) Check whether male users are having maximum followers or female users.

Ans) -- From Table we see Female (76121) followers are more than Male(22792).

SELECT
gender Gender,
SUM(COUNT(gender)) OVER(PARTITION BY gender) Followers
FROM
users_data
GROUP BY gender;

7) Calculate the total users those

1. Uses Profile Picture in their Profile
2. Uses Application for Ecommerce platform
3. Uses Android app
4. Uses ios app

Ans)

SELECT
COUNT(*) Count, 'Uses Profile Picture in their Profile'
FROM
users_data
WHERE
hasProfilePicture = 'True'
UNION SELECT
COUNT(*), 'Uses Application for Ecommerce platform'
FROM
users_data
WHERE
hasAnyApp = 'True'
UNION SELECT
COUNT(*), 'Uses Android app'
FROM
users_data
WHERE
hasAndroidApp = 'True'
UNION SELECT
COUNT(*), 'Uses IOS app'
FROM
users_data
WHERE
hasIosApp = 'True';

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

Ans)

SELECT
country Country, COUNT(productsBought) Total_no_of_Buyers
FROM
users_data
WHERE
productsBought <> 0
GROUP BY country
ORDER BY Total_no_of_Buyers DESC;

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

Ans)

SELECT
country Country, COUNT(productsSold) Total_no_of_sellers
FROM
users_data
WHERE
productsSold <> 0
GROUP BY country
ORDER BY Total_no_of_sellers;

10) Display name of top 10 countries having maximum products pass rate.

Ans)

SELECT
country Country, SUM(productsPassRate) Pass_Rate
FROM
users_data
GROUP BY country
ORDER BY Pass_Rate DESC
LIMIT 10;

11) Calculate the number of users on an ecommerce platform for different language choices.

Ans)

SELECT
language, COUNT(type) Buyers
FROM
users_data
GROUP BY language
ORDER BY Buyers DESC;

12) Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform.

Ans) -- It appears that choice of Female users is ' to Put the product in wish list '

SELECT
'Females with wishlist', COUNT(gender) AS Numbers
FROM
users_data
WHERE
gender = 'F' && productsWished <> 0
UNION SELECT
'Females Liking product ', COUNT(productsSold) AS Numbers
FROM
users_data
WHERE
gender = 'M' && socialProductsLiked <> 0;

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

Ans)-- Choice of male users is 'to buy '

SELECT
'Male Buyers', COUNT(gender) AS Numbers
FROM
users_data
WHERE
gender = 'M' && productsBought <> 0
UNION SELECT
'Male sellers', COUNT(productsSold) AS Numbers
FROM
users_data
WHERE
gender = 'M' && productsSold <> 0;

14) Which country is having maximum number of buyers?

Ans) We see 'France with 3573 buyers' has the highest buyers.

SELECT
country, SUM(productsBought) Buyers
FROM
users_data
GROUP BY country
ORDER BY Buyers DESC
LIMIT 10;

15) List the name of 10 countries having zero number of sellers.

Ans)

SELECT
country, SUM(productsListed) sellers
FROM
users_data
GROUP BY country
ORDER BY sellers
LIMIT 10;

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

Ans)

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.

Ans)

SELECT
'Not loggid in since 100 days',
COUNT(gender) AS Female_Users
FROM
users_data
WHERE
gender = 'F'
&& daysSinceLastLogin >= 100;

18) Display the number of female users of each country at ecommerce platform.

Ans)

SELECT
country, COUNT(gender) AS FemaleUsers
FROM
users_data
WHERE
gender = 'F'
GROUP BY country
ORDER BY FemaleUsers DESC;

19) Display the number of male users of each country at ecommerce platform.

Ans)

SELECT
country, COUNT(gender) AS MaleUsers
FROM
users_data
WHERE
gender = 'M'
GROUP BY country
ORDER BY MaleUsers DESC;

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

Ans)

SELECT
country,
AVG(productsSold) AS AvgSold,
AVG(productsBought) AS AvgBought
FROM
users_data
WHERE
gender = 'M'
GROUP BY country
ORDER BY AvgSold DESC;