Project 1
#Q1 Create new schema as ecommerce
-- create database ecommerce;
#Q2
use ecommerce;
#Q3 Run SQL command to see the structure of table
desc users_data;
#Q4 Run SQL command to select first 100 rows of the database
SELECT
*
FROM
users_data
LIMIT 100;
#Q5 How many distinct values exist in table for field country and language
SELECT
COUNT(DISTINCT country) Country, COUNT(DISTINCT language)
FROM
users_data;
SELECT DISTINCT
country, language
FROM
users_data
ORDER BY country;
-- there is no data for india so i am adding the data
insert into users_data (country, language) values ('India','hi');
#Q6 Check whether male users are having maximum followers or female users.
SELECT
gender, SUM(socialNbFollowers)
FROM
users_data
GROUP BY gender;
#Q7 Calculate the total users those
-- a)Uses Profile Picture in their Profile
SELECT
COUNT(hasProfilePicture) Users_with_profile_picture
FROM
users_data
where hasProfilePicture = 'true' ;
-- b)Uses Application for Ecommerce platform
SELECT
COUNT(hasAnyApp) users_with_Anyapp
FROM
users_data
WHERE
hasAnyApp = 'true';
-- c)Uses Android app
SELECT
COUNT(hasAndroidApp) users_with_AndroidApp
FROM
users_data
WHERE
hasAndroidApp = 'true';
select hasAndroidApp from users_data;
-- d)Uses ios app
SELECT
COUNT(hasIosApp) users_with_IosApp
FROM
users_data
WHERE
hasIosApp = 'true';
-- Q8 Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers. (Hint: consider only those users having at least 1 product bought.)
SELECT
country, COUNT(productsBought) Buyers
FROM
users_data
GROUP BY country
ORDER BY Buyers DESC;
-- Q9 Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers. (Hint: consider only those users having at least 1 product sold.)
SELECT
country, COUNT(productsSold) Sellers
FROM
users_data
GROUP BY country
ORDER BY Sellers ASC;
-- Q10 Display name of top 10 countries having maximum products pass rate.
SELECT
country
FROM
users_data
GROUP BY country
HAVING MAX(productsPassRate)
LIMIT 10;
-- Q11 Calculate the number of users on an ecommerce platform for different language choices.
SELECT
language, COUNT(language)
FROM
users_data
GROUP BY language;
-- Q12 Check the choice of female users about putting the product in a wishlist or to like socially on an ecommerce platform. (Hint: use UNION to answer this question.)
SELECT
SUM(productsWished) WishList
FROM
users_data
WHERE
gender = 'F'
UNION SELECT
SUM(socialProductsLiked) LikeSocially
FROM
users_data
WHERE
gender = 'F';
-- Q13 Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT
COUNT(productssold), COUNT(productsbought)
FROM
users_data
WHERE
gender = 'M';
-- Q14 Which country is having maximum number of buyers?
SELECT
country, MAX(productsBought) Max_No_of_buyers
FROM
users_data
GROUP BY country
ORDER BY MAX(productsBought) DESC
LIMIT 1;
-- Q15 List the name of 10 countries having zero number of sellers
SELECT
country, productssold
FROM
users_data
WHERE
productssold = 0
LIMIT 10;
-- Q16 Display record of top 110 users who have used ecommerce platform recently.
SELECT
*
FROM
users_data
GROUP BY daysSinceLastLogin
HAVING daysSinceLastLogin = MIN(daysSinceLastLogin)
ORDER BY daysSinceLastLogin ASC
LIMIT 110;
-- Q17 Calculate the number of female users those who have not logged in since last 100 days.
SELECT
COUNT(type)
FROM
users_data
WHERE
gender = 'F'
AND daysSinceLastLogin >= 100;
-- Q18 Display the number of female users of each country at ecommerce platform.
SELECT
country, COUNT(gender) Female_users
FROM
users_data
WHERE
gender = 'F'
GROUP BY country;
-- Q19 Display the number of male users of each country at ecommerce platform.
SELECT
country, COUNT(gender) Male_users
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;
-- Q20 Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT
country,
AVG(productsSold) Products_Sold,
AVG(productsBought) Products_Bought
FROM
users_data
WHERE
gender = 'M'
GROUP BY country;