Challenge

Project 1

YYeshwanth Kaipa14 Sept 2022

#1
CREATE DATABASE ecommerce;
USE ecommerce;
#2
-- imported the data set as mentioned--

#3
desc users_data;
#4
select * from users_data limit 100;
#5
select count(distinct language) language,count(distinct country) country from users_data;
#6
select'description', 'count' union select 'Male count', sum(socialNBfollowers) from users_data where (gender ='m') union (select 'female count',sum(socialNBfollowers) from users_data where gender ='f');
select sum(socialNBfollowers) from users_data where gender ='f';
#7
use ecommerce;
select 'index', 'count' union select 'has profile pic', count(hasProfilePicture) from users_data where hasProfilePicture ='TRUE'union
SELECT 'has any app', count(hasAnyApp) from users_data where hasAnyApp ='True' union
select 'has android app',count(hasAndroidApp) from users_data where hasAndroidApp='true' union
select 'has ios app',count(hasIosApp) from users_data where hasIosApp ='true';
#8
select country, count(type) total from users_data where productsbought !=0 group by country order by total desc;

#9
select country, count(type) total from users_data where productssold !=0 group by country order by total desc;

#10

select country, count(productsPassRate) PPR from users_data where productspassrate !=0 group by country order by PPR desc limit 10;

#11
select count(type) DLC from users_data where language != 'en';

#12
select 'description','count' union select 'productsWished', sum(productsWished) from users_data where gender ='f' union select 'products like socially', sum(socialProductsLiked) from users_data where gender ='f';

#13
SELECT 'Description', 'count'
UNION SELECT
'Sellers', COUNT(productsListed)
FROM
users_data
WHERE
gender = 'M' AND productsListed > 0
UNION SELECT
'Buyers', COUNT(productsBought)
FROM
users_data
WHERE
gender = 'M' AND productsBought > 0;
#14
select country, count(type) maximum_buyers from users_data where productsBought != 0 group by country order by maximum_buyers desc limit 1;

#15
select country, sum(productslisted) list from users_data group by country order by list limit 10;
#16
select identifierHash, type, daysSinceLastLogin from users_data order by daysSinceLastLogin limit 110;

#17
select count(type) female_users from users_data where (daysSinceLastLogin > 100 and gender ='f');

#18
select country,count(type) from users_data where gender ="f" group by country;

#19
select country, count(type) from users_data where gender = 'm' group by country;

#20
select country, Avg(productsSold) sold,Avg(productsBought) bought from users_data where gender ="M" group by country;