Project 1

Answer:

Q1.

create database ecommerce;
use ecommerce;

Q3.

desc users_data;

Q4. select * from users_data limit 100;

Q5.

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

Distinct values for field country = 200 and language = 5

Q6.

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

Female has maximum followers

Q7.

a. SELECT
COUNT(hasProfilePicture)
FROM
users_data
WHERE
hasProfilePicture = 'true';

Total 97018 Uses Profile Picture in their Profile

b. SELECT
COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'true';

Total 26174 Uses Application for Ecommerce platform

c. SELECT
COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'true';

Total 4819 Uses Android app

d. SELECT
COUNT(hasIosApp)
FROM
users_data
WHERE
hasIosApp = 'True';

Total 21527 Uses ios app

Q8.

SELECT
country, COUNT(productsBought) total_no_of_buyers
FROM
users_data
WHERE
productsBought >= 1
GROUP BY country
ORDER BY total_no_of_buyers DESC;

Q9.

SELECT
country, COUNT(productsSold) total_no_of_seller
FROM
users_data
WHERE
productsSold >= 1
GROUP BY country
ORDER BY total_no_of_seller ASC;

Q10.

SELECT
country, MAX(productsPassRate)
FROM
users_data
GROUP BY country
ORDER BY MAX(productsPassRate)
desc limit 10;

Q11.

SELECT
language, COUNT(type) total
FROM
users_data
GROUP BY language;

Q12.

SELECT
gender, COUNT(productsWished)
FROM
users_data
WHERE
gender = 'F' AND productsWished >= 1
UNION SELECT
gender, COUNT(socialproductsliked)
FROM
users_data
WHERE
gender = 'F'
AND socialproductsliked >= 1;

Q13.

SELECT
gender, COUNT(productsSold)
FROM
users_data
WHERE
gender = 'M' AND productsSold >= 1
UNION SELECT
gender, COUNT(productsBought)
FROM
users_data
WHERE
gender = 'M' AND productsBought >= 1;

Q14.

SELECT
country, COUNT(productsbought) AS maximum_no_buyers
FROM
users_data
GROUP BY country
ORDER BY maximum_no_buyers DESC;

Q15.

SELECT
country
FROM
users_data
WHERE
productsSold = 0
LIMIT 10;

Q16.

SELECT
*
FROM
users_data
ORDER BY daysSinceLastLogin ASC
LIMIT 110;

Q17.

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

Q18.

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

Q19.

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

Q20.

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