Project 1

-- Q1

create schema ecommerce;

-- Q2

use ecommerce;

select * from users_data;

-- Q3

desc users_data;

-- Q4

SELECT * FROM users_data

LIMIT 100;

-- Q5

SELECT COUNT(DISTINCT country) as dist_country,COUNT(DISTINCT language) as dist_language FROM users_data;

-- Q6

SELECT SUM(socialNbFollowers),gender FROM users_data GROUP BY gender;

-- Q7

-- (a)Uses Profile Picture in their Profile

USE ecommerce;

SELECT COUNT(*) FROM users_data WHERE hasProfilePicture ="True";

-- (b)Uses Application for Ecommerce platform

USE ecommerce;

SELECT COUNT(*) FROM users_data WHERE hasAnyApp ="True";

-- (c)Uses Android app

USE ecommerce;

SELECT COUNT(*) FROM users_data WHERE hasAndroidApp ="True";

-- (d)Uses ios app

USE ecommerce;

SELECT COUNT(*) FROM users_data WHERE hasIosApp ="True";

-- Q8

USE ecommerce;

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

-- Q9

USE ecommerce;

SELECT country,AVG(productsSold) FROM users_data GROUP BY country ORDER BY AVG(productsSold) ASC;

-- Q 10

USE ecommerce;

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

-- Q 11

USE ecommerce;

SELECT language, COUNT(*) FROM users_data GROUP BY language;

-- Q 12

USE ecommerce;

SELECT SUM(productsWished),SUM(socialProductsLiked) FROM users_data WHERE gender='F';

-- Q 13

USE ecommerce;

SELECT SUM(productsSold),SUM(productsBought) FROM users_data WHERE gender='M';

-- Q 14

USE ecommerce;

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

-- Q 15

USE ecommerce;

SELECT productsSold,country FROM users_data WHERE productsSold=0 LIMIT 10;

-- Q 16

USE ecommerce;

SELECT daysSinceLastLogin FROM users_data ORDER BY daysSinceLastLogin ASC LIMIT 110;

-- Q 17

USE ecommerce;

SELECT gender,COUNT(daysSinceLastLogin) FROM users_data WHERE gender='F' AND daysSinceLastLogin >100;

-- Q 18

USE ecommerce;

SELECT country, COUNT(*) FROM users_data WHERE gender='F' ORDER BY country;

-- Q 19

USE ecommerce;

SELECT country, COUNT(*) FROM users_data WHERE gender='M' GROUP BY country;

-- Q 20

USE ecommerce;

SELECT AVG(productsSold),AVG(productsBought),country FROM users_data WHERE gender='M' GROUP BY country;