Project 1
- Create new schema as ecommerce
create schema ecommerce;
2. Import .csv file users_data into MySQL
and
3. Run SQL command to see the structure of table
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 distinct country, language from users_data;
6. Check whether male users are having maximum followers or female users
select gender,
max(socialnbfollowers) maximum_followers,
count(socialnbfollowers) total_count
from users_data
group by gender;
7. Calculate the total users those
a. Uses Profile Picture in their Profile
select type, hasprofilepicture, count(type) Total_profilepicture from users_data where hasprofilepicture = 'True' ;
b. Uses Application for Ecommerce platform
select type, hasanyapp, count(type) Total_hasanyapp from users_data where hasanyapp = 'True' ;
c. Uses Android app
select type, hasandroidapp, count(type) Total_hasandroidapp from users_data where hasandroidapp = 'True' ;
d. Uses ios app
select type, hasiosapp, count(type) Total_hasiosapp 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
select country, count(productsbought) total_buyers
from users_data
where productsbought > 0
group by country
order by total_buyers desc ;
9. Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers
select country, count(productssold) total_sellers
from users_data
where productssold > 0
group by country
order by total_sellers asc;
10. Display name of top 10 countries having maximum products pass rate
Select country, max(productspassrate) maximum_prouctspassrate
from users_data
group by country
order by maximum_prouctspassrate desc
limit 10;
11. Calculate the number of users on an ecommerce platform for different language choices
Select count(type) number_of_users, language from users_data group by language;
12. Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform
Select gender, count(productswished) from users_data where productswished > 0 and gender = 'F'
union
select gender, count(socialproductsliked) from users_data where socialproductsliked > 0 and gender = 'F';
13. Check the choice of male users about being seller or buyer
select gender, count(productsbought) from users_data where productsbought > 0 and gender = 'M'
union
select gender, count(productssold) from users_data where productssold > 0 and gender = 'M'
14. Which country is having maximum number of buyers?
select country, count(productsbought)maximum_buyers
from users_data
group by country order by maximum_buyers desc
limit 1;
16. Display record of top 110 users who have used ecommerce platform recently.
select * from users_data order by dayssincelastlogin asc limit 110;
17. 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;
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' 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' 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_product_sold,
avg(productsbought) average_product_bought
from users_data
where gender = 'M'
group by country ;