Project 1
#Q1
create database if not exists ecommerce;
#Q2
use ecommerce;
SELECT
*
FROM
users_data;
describe users_data;
SELECT
*
FROM
users_data
LIMIT 100;
SELECT
COUNT(DISTINCT country) distinct_values_country
FROM
users_data;
SELECT
COUNT(DISTINCT language) distinct_values_language
FROM
users_data;
SELECT
COUNT(IF(gender = 'M', 1, NULL)) Male_Users
FROM
users_data;
SELECT
COUNT(IF(gender = 'F', 1, NULL)) Female_Users
FROM
users_data;
SELECT
COUNT(hasProfilePicture) User_with_Profile_Picture
FROM
users_data;
SELECT
COUNT(hasAnyApp) Uses_Application
FROM
users_data;
SELECT
COUNT(hasAndroidApp) Uses_Android_App
FROM
users_data;
SELECT
COUNT(hasIosApp) Uses_ios_app
FROM
users_data;
SELECT
productsBought total_number_of_buyers, country
FROM
users_data
WHERE
productsBought > 0;
SELECT
country, COUNT(productsBought) AS totalProductsBought
FROM
users_data
GROUP BY country
ORDER BY totalProductsBought DESC;
SELECT
productsSold total_number_of_sellers, country
FROM
users_data
WHERE
productsSold > 0;
SELECT
country, COUNT(productsSold) AS total_number_sellers
FROM
users_data
GROUP BY country
ORDER BY total_number_sellers ASC;
SELECT
country, COUNT(productsPassRate) AS pass_rate
FROM
users_data
GROUP BY country
ORDER BY pass_rate DESC
LIMIT 10;
SELECT
language, COUNT(language) different_language_choices
FROM
users_data
GROUP BY language;
SELECT
gender, productsWished product_in_a_wishlist
FROM
users_data
WHERE
gender = 'F'
UNION SELECT
gender, socialProductsLiked
FROM
users_data
WHERE
gender = 'F';
SELECT
gender,
productsWished choice_of_male_users_about_being_seller_or_buyer
FROM
users_data
WHERE
gender = 'M'
UNION SELECT
gender, socialProductsLiked
FROM
users_data
WHERE
gender = 'M';
SELECT
country,
MAX(productsBought) country_is_having_maximum_number_of_buyers,
COUNT(productsBought) Count_number_of_buyers
FROM
users_data
GROUP BY country
ORDER BY country_is_having_maximum_number_of_buyers DESC
LIMIT 1;
SELECT
country,
productsSold name_of_10_countries_having_zero_number_of_sellers
FROM
users_data
WHERE
productsSold = 0
LIMIT 10;
SELECT
daysSinceLastLogin User_days_Since_Last_Login
FROM
users_data
ORDER BY daysSinceLastLogin ASC
LIMIT 110;
SELECT
COUNT(gender) number_of_female_users, gender
FROM
users_data
WHERE
gender = 'F'
AND daysSinceLastLogin > 100
ORDER BY daysSinceLastLogin
LIMIT 100;
SELECT
COUNT(gender) number_of_female_users, country
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
SELECT
COUNT(gender) number_of_female_users, country
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
SELECT
AVG(productsSold) average_number_of_products_sold,
AVG(productsBought) average_number_of_products_bought,
COUNT(gender) Number_Of_male_users,
country
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;