Project 1
1.Create new schema as ecommerce
CREATE SCHEMA ecommerce;
USE ecommerce;
2.Import.csv file users_data into MYSQL
I have import the file in navigator by using the option load spatial data of ecommerce options
3.Run SQL command to see the structure of table
DESCRIBE ecommerce.users_data_sample;
4.Run SQL command to select first 100 rows of the database
SELECT *FROM ecommerce.users_data_sample limit 100;
5.How many distinct values exist in table for field country and language
SELECT COUNT(Distinct country) AS distinct_countries FROM ecommerce.users_data_sample;
SELECT COUNT(Distinct language) AS distinct_languages FROM ecommerce.users_data_sample;
6.Check whether male users are having maximum followers or female users.
SELECT gender, AVG(followers) AS avg_followers
FROM ecommerce.users_data_sample
GROUP BY gender
ORDER BY avg_followers DESC;
7.Calculate the total users those
a. Uses Profile Picture in their Profile
SELECT COUNT(*) AS profile_pic_users
FROM ecommerce.users_data_sample
WHERE profile_picture IS NOT NULL;
b. Uses Application for Ecommerce platform
SELECT COUNT(*) AS app_users
FROM ecommerce.users_data_sample
WHERE platform_used = 'App';
c. Uses Android app
SELECT COUNT(*) AS android_users
FROM ecommerce.users_data_sample
WHERE device_type = 'Android';
d.Uses ios app
SELECT COUNT(*) AS ios_users
FROM ecommerce.users_data_sample
WHERE device_type = 'iOS';
8.Calculate the total number of buyers for each country and sort the result in descending order of total number of buyers.
SELECT country, COUNT(*) AS total_buyers
FROM ecommerce.users_data_sample
WHERE total_purchases > 0
GROUP BY country
ORDER BY total_buyers DESC;
9.Calculate the total number of sellers for each country and sort the result in ascending order of total number of sellers.
SELECT country, COUNT(*) AS total_sellers
FROM ecommerce.users_data_sample
WHERE total_sales > 0
GROUP BY country
ORDER BY total_sellers ASC;
10.Display name of top 10 countries having maximum products pass rate.
SELECT country, AVG(product_pass_rate) AS avg_pass_rate
FROM ecommerce.users_data_sample
GROUP BY country
ORDER BY avg_pass_rate DESC
LIMIT 10;
11.Calculate the number of users on an ecommerce platform for different language choices.
SELECT language, COUNT(*) AS user_count
FROM ecommerce.users_data_sample
GROUP BY language;
12.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 'Wishlist' AS choice_type, COUNT(*) AS total
FROM ecommerce.users_data_sample
WHERE gender = 'female' AND wishlist = 1
UNION
SELECT 'Social Like' AS choice_type, COUNT(*) AS total
FROM ecommerce.users_data_sample
WHERE gender = 'female' AND social_like = 1;
13.Check the choice of male users about being seller or buyer. (Hint: use UNION to solve this question.)
SELECT 'Buyer' AS role, COUNT(*) AS total
FROM ecommerce.users_data_sample
WHERE gender = 'male' AND total_purchases > 0
UNION
SELECT 'Seller' AS role, COUNT(*) AS total
FROM ecommerce.users_data_sample
WHERE gender = 'male' AND total_sales > 0;
14.Which country is having maximum number of buyers?
SELECT country, COUNT(*) AS total_buyers
FROM ecommerce.users_data
WHERE total_purchases > 0
GROUP BY country
ORDER BY total_buyers DESC
LIMIT 1;
15.List the name of 10 countries having zero number of sellers.
SELECT country
FROM ecommerce.users_data_sample
GROUP BY country
HAVING SUM(CASE WHEN total_sales > 0 THEN 1 ELSE 0 END) = 0
LIMIT 10;
16.Display record of top 110 users who have used ecommerce platform recently.
SELECT *
FROM ecommerce.users_data_sample
ORDER BY last_active DESC
LIMIT 110;
17.Calculate the number of female users those who have not logged in since last 100 days.
SELECT COUNT(*) AS inactive_females
FROM ecommerce.users_data_sample
WHERE gender = 'female' AND DATEDIFF(CURDATE(), last_active) > 100;
18.Display the number of female users of each country at ecommerce platform.
SELECT country, COUNT(*) AS total_females
FROM ecommerce.users_data_sample
WHERE gender = 'female'
GROUP BY country;
19.Display the number of male users of each country at ecommerce platform.
SELECT country, COUNT(*) AS total_males
FROM ecommerce.users_data_sample
WHERE gender = 'male'
GROUP BY country;
20.Calculate the average number of products sold and bought on ecommerce platform by male users for each country.
SELECT country,
AVG(total_sales) AS avg_sold,
AVG(total_purchases) AS avg_bought
FROM ecommerce.users_data_sample
WHERE gender = 'male'
GROUP BY country;
