Project 1
# Answer 1
create database ecommerce;
# Answer 2
use ecommerce;
SHOW TABLES;
# Answer 3
DESCRIBE USERS_DATA;
# Answer 4
SELECT * FROM USERS_DATA LIMIT 100;
# Answer 5
SELECT DISTINCT COUNTRY,LANGUAGE FROM USERS_DATA;
SELECT COUNT(DISTINCT COUNTRY) FROM USERS_DATA;
SELECT COUNT(DISTINCT LANGUAGE) FROM USERS_DATA;
SELECT COUNT(DISTINCT COUNTRY,LANGUAGE) FROM USERS_DATA;
# Answer 6
SELECT COUNT(SOCIALNBFOLLOWERS) FROM USERS_DATA WHERE GENDER="M";
# 22792
SELECT COUNT(SOCIALNBFOLLOWERS) FROM USERS_DATA WHERE GENDER="f";
# 76121
# female users are having maximum followers
# Answer 7
select count(hasprofilepicture) from users_data where hasprofilepicture="true";
#97018
select count(hasanyapp) from users_data where hasanyapp="true";
#26174
select count(hasandroidapp) from users_data where hasandroidapp="true";
#4819
select count(hasiosapp) from users_data where hasiosapp="true";
#21527
select count(identifierhash) from users_data where hasprofilepicture="true" and hasanyapp="true" and hasandroidapp="true" and hasiosapp="true" ;
#148
# Answer 8
select country,sum(productsbought) from users_data where productsbought >0 group by country order by sum(productsbought) desc ;
9.select country,sum(productsSold) from users_data where productsSold >0 group by country order by sum(productsSold) asc ;
# Answer 10
select country,max(productspassrate) from users_data group by country order by max(productspassrate) desc limit 10;
# Answer 11
select language, count(identifierhash) from users_data group by language;
# Answer 12
select count(identifierhash) from users_data where gender="f" and productswished >0
union
select count(identifierhash) from users_data where gender="f" and hasanyapp="true";
# Answer 13
select count(identifierhash) from users_data where gender="m" and productsbought=1
union
select count(identifierhash) from users_data where gender="m" and productssold=1;
# Answer 14
select country,max(productsbought) from users_data group by country order by max(productsbought) desc limit 1;
# Answer 15
select country,PRODUCTSSOLD from users_data WHERE PRODUCTSSOLD=0 group by country LIMIT 10 ;
# Answer 16
select * FROM users_data WHERE HASANYAPP= "TRUE" order by DAYSSINCELASTLOGIN ASC LIMIT 110;
# Answer 17
select * FROM users_data WHERE gender="f" and HASANYAPP= "true" and DAYSSINCELASTLOGIN>=100;
# Answer 18
select country, count(identifierhash) FROM users_data WHERE gender="f"and HASANYAPP= "true" group by country;
# Answer 19
select country, count(identifierhash) FROM users_data WHERE gender="m"and HASANYAPP= "true" group by country;
# Answer 20
select country, avg(productssold) FROM users_data WHERE gender="m"and HASANYAPP= "true" group by country;
THANK YOU!