Project 1 - Business model Customer to Customer (C2C)
-- Q1
create database ecommerce;
use ecommerce;
-- Q3
desc users_data;
-- Q4
SELECT
*
FROM
users_data
LIMIT 100;
-- Q5
select distinct(country) Dcountry from users_data;
select distinct(language) Dlanguage from users_data;
SELECT
COUNT(DISTINCT (country)) Dcountry,
COUNT(DISTINCT (language)) Dlanguage
FROM
users_data;
-- Q6
select * from users_data;
select gender,socialNbFollowers from users_data;
SELECT
gender, MAX(socialNbFollowers) Maxfollower
FROM
users_data
GROUP BY gender;
-- Q7
-- a
select * from users_data;
SELECT
COUNT(hasProfilePicture)
FROM
users_data
WHERE
hasProfilePicture = 'True';
-- b
SELECT
COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'True';
-- c
SELECT
COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'True';
-- d
SELECT
COUNT(hasIosApp)
FROM
users_data
WHERE
hasIosApp = 'True';
-- Q8
select * from users_data;
select country from users_data;
SELECT
COUNT(productsBought), country
FROM
users_data
WHERE
productsBought >= 1
GROUP BY country
ORDER BY COUNT(productsBought) DESC;
-- Q9
SELECT
COUNT(productsSold), country
FROM
users_data
WHERE
productsSold >= 1
GROUP BY country
ORDER BY COUNT(productsSold);
-- Q10
select * from users_data;
select productsPassRate from users_data;
SELECT
country, MAX(productsPassRate) Max
FROM
users_data
GROUP BY country
ORDER BY Max DESC
LIMIT 10;
-- Q11
select * from users_data;
SELECT
COUNT(type), language
FROM
users_data
WHERE
type = 'user'
GROUP BY language;
-- Q12
select * from users_data;
select * from users_data where gender = 'F';
SELECT
'Liked', socialProductsLiked
FROM
users_data
WHERE
gender = 'F'
UNION SELECT
'wished', productsWished
FROM
users_data
WHERE
gender = 'F';
-- Q13
select * from users_data;
select * from users_data where gender='M';
SELECT
'selller', productsSold
FROM
users_data
WHERE
gender = 'M'
UNION SELECT
'buyer', productsBought
FROM
users_data
WHERE
gender = 'M';
-- Q14
SELECT
country, MAX(productsBought) Maxbuyer
FROM
users_data
GROUP BY country
ORDER BY Maxbuyer DESC;
-- Belgique - 405
-- Q15
select * from users_data;
SELECT DISTINCT
(country), productsSold
FROM
users_data
WHERE
productsSold = 0
LIMIT 10;
-- Q16
select * from users_data;
SELECT
*
FROM
users_data
WHERE
daysSinceLastLogin = 11
LIMIT 110;
-- Q17
SELECT
COUNT(*)
FROM
users_data
WHERE
daysSinceLastLogin >= 100
AND gender = 'F'
LIMIT 110;
-- 70212
-- Q18
select * from users_data;
select country from users_data where gender='F';
SELECT
country, COUNT(gender) Nooffemale
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
SELECT count(distinct(country)) from users_data where gender = 'F';
-- Q19
SELECT
country, COUNT(gender) Nooffemale
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
-- Q20
select * from users_data;
select country,avg(productsSold) Averagesold, avg(productsBought) Averagebought from users_data where gender= 'M' group by country;