Project 1
3.SELECT * FROM ecommerce.users_data;
4.SELECT *
FROM users_data
LIMIT 100;
5.SELECT DISTINCT country,language
FROM users_data
6.SELECT COUNT(socialNbFollowers),gender
FROM users_data
GROUP BY gender
7a.SELECT COUNT(hasProfilePicture) FROM users_data
WHERE hasProfilePicture='TRUE'
7b.SELECT COUNT(hasAnyApp)
FROM users_data
WHERE hasAnyApp = 'True'
7c.SELECT COUNT(hasAndroidApp) FROM users_data
WHERE hasAndroidApp='True'
8.SELECT SUM(productsBought) AS smp,country
FROM users_data
WHERE productsBought > 0
GROUP BY country
ORDER BY smp DESC
9.SELECT SUM(productsSold) AS smps,country
FROM users_data
WHERE productsSold>0
GROUP BY country
ORDER BY smps ASC
10.SELECT MAX(productsPassRate) AS ppr,country
FROM users_data
WHERE productsPassRate>0
GROUP BY country
limit 10;
11.SELECT COUNT(language) AS cl,country
FROM users_data
GROUP BY country
12.SELECT 'socialProductsLiked' AS Type,COUNT(*)
FROM users_data
WHERE gender = 'F'
UNION
SELECT 'productsWished' AS Type,COUNT(*)
FROM users_data
WHERE gender = 'F'
13.SELECT 'productsBought' AS type,COUNT(*),gender
FROM users_data
GROUP BY 'productsBought',gender
HAVING gender = 'M'
UNION
SELECT 'productsSold' AS type,COUNT(*),gender
FROM users_data
GROUP BY 'productsSold',gender
HAVING gender = 'M'
14.SELECT MAX(productsBought),country
FROM users_data
GROUP BY country
limit 1
15.SELECT productsSold AS pS,country
FROM users_data
GROUP BY pS,country
HAVING pS = 0
limit 10
17.SELECT daysSinceLastLogin AS dSL,identifierHash AS idf
FROM users_data
WHERE gender = 'F'
GROUP BY dSL,idf
HAVING dSL > 100
limit 100
18.SELECT COUNT(gender) AS cg,country
FROM users_data
WHERE gender = 'F'
GROUP BY country
19.SELECT COUNT(gender) AS cg,country
FROM users_data
WHERE gender = 'M'
GROUP BY country
20.SELECT AVG(productsBought) AS AVERAGE ,country,'productsBought' AS TYPE
FROM users_data
WHERE gender = 'M'
GROUP BY country,'productsBought'
UNION
SELECT AVG(productsSold) AS TYPE,country,'productsSold' AS TYPE
FROM users_data
WHERE gender = 'M'
GROUP BY country,'productsSold'