Project 1

Q1)

CREATE SCHEMA ecommerce;
USE ecommerce;

Q2)

imported csv file in the table using Table Data import Wizard> chooesn the path > next >create new table> next >next > finished

SELECT
*
FROM
users_data;

Q3)

DESC users_data;

Q4)

SELECT
*
FROM
users_data
LIMIT 100;

Q5)

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

Q6)

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

Q7)

SELECT
'total_users_have_profile', COUNT(hasProfilePicture) total
FROM
users_data
WHERE
hasProfilePicture = 'true'
UNION SELECT
'total_users_apps', COUNT(hasAnyApp)
FROM
users_data
WHERE
hasAnyApp = 'true'
UNION SELECT
'total_users_have_androidapp', COUNT(hasAndroidApp)
FROM
users_data
WHERE
hasAndroidApp = 'true'
UNION SELECT
'total_users_have_Iosapp', COUNT(hasIosApp)
FROM
users_data
WHERE
hasIosApp = 'true';

Q8)

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

Q9)

SELECT
country, COUNT(productsSold) seller
FROM
users_data
WHERE
productsSold > 0
GROUP BY country
ORDER BY COUNT(productsSold);

Q10)

SELECT
country, productsPassRate
FROM
users_data
ORDER BY productsPassRate DESC
LIMIT 10;

Q11)

SELECT
language , COUNT(hasAnyApp) ecommerce_platform
FROM
users_data
GROUP BY language;

Q12)

SELECT
'wishlist', COUNT(productsWished) choices
FROM
users_data
WHERE
gender = 'f' AND productsWished > 0
UNION SELECT
'like_socially', COUNT(socialProductsLiked) choice
FROM
users_data
WHERE
gender = 'f' AND socialProductsLiked > 0;

Q13)

SELECT
'seller', COUNT(productsSold) choices
FROM
users_data
WHERE
gender = 'm' AND productsSold > 0
UNION SELECT
'buyer', COUNT(productsBought) choice
FROM
users_data
WHERE
gender = 'm' AND productsBought > 0;

Q14)

SELECT
COUNT(productsBought), country
FROM
users_data
GROUP BY country
ORDER BY COUNT(productsBought) DESC
LIMIT 1;

Q 15)

SELECT
country
FROM
users_data
WHERE
productsSold = 0
GROUP BY country
ORDER BY COUNT(productsSold) DESC
LIMIT 10;

## we can give the count also
SELECT
country, COUNT(productsSold)
FROM
users_data
WHERE
productsSold = 0
GROUP BY country
ORDER BY COUNT(productsSold) DESC
LIMIT 10;

Q16)

SELECT
*
FROM
users_data
ORDER BY daysSinceLastLogin
LIMIT 110;

Q17)

SELECT
COUNT(daysSinceLastLogin) last_login
FROM
users_data
WHERE
daysSinceLastLogin >= 100
AND gender = 'f'
ORDER BY daysSinceLastLogin;

Q18)

SELECT
COUNT(hasAnyApp) female_users, country
FROM
users_data
WHERE
gender = 'f' AND hasAnyApp = 'true'
GROUP BY country;
## this also get the results
SELECT
COUNT(gender) female_users, country
FROM
users_data
WHERE
gender = 'f' AND hasAnyApp = 'true'
GROUP BY country;

Q19)

SELECT
COUNT(hasAnyApp) male_users, country
FROM
users_data
WHERE
gender = 'm' AND hasAnyApp = 'true'
GROUP BY country;
## this also will work result same answer
SELECT
COUNT(gender) male_users, country
FROM
users_data
WHERE
gender = 'm' AND hasAnyApp = 'true'
GROUP BY country;

Q20)

SELECT
AVG(productsSold) avg_sold, AVG(productsBought) Avg_bought
FROM
users_data
WHERE
hasAnyApp = 'true' AND gender = 'm'
GROUP BY country;