Project 1
1. Create a New Schema
First, create a new schema named ecommerce in MySQL.
sql
CREATE SCHEMA ecommerce;
USE ecommerce;
2. Import users_data.csv into MySQL
Right-click on the ecommerce schema.
Select Table Data Import Wizard.
Provide the path of the users_data.csv file.
Click Next, choose the option to "Create a new table", and select "Delete if exists".
Continue through the wizard to complete the import.
3. Run SQL Command to See the Structure of the Table
After importing, check the structure of the table:
sql
DESCRIBE users_data;
4. Run SQL Command to Select First 100 Rows of the Database
Select the first 100 rows from the table:
sql
SELECT * FROM users_data LIMIT 100;
5. Count Distinct Values for country and language
Find the distinct values for country and language:
sql
SELECT COUNT(DISTINCT country) AS distinct_countries, COUNT(DISTINCT language) AS distinct_languages FROM users_data;
6. Check Whether Male or Female Users Have Maximum Followers
Count followers by gender:
sql
SELECT gender, SUM(followers_count) AS total_followers
FROM users_data
GROUP BY gender
ORDER BY total_followers DESC
LIMIT 1;
7. Calculate Total Users Based on Specific Criteria
Users with Profile Picture
sql
SELECT COUNT(*) AS total_users_with_profile_pic
FROM users_data
WHERE profile_picture IS NOT NULL;
Users Using Application for E-commerce Platform
sql
SELECT COUNT(*) AS total_users_using_app
FROM users_data
WHERE uses_ecommerce_app = 1;
Users Using Android App
sql
SELECT COUNT(*) AS total_android_users
FROM users_data
WHERE uses_android_app = 1;
Users Using iOS App
sql
SELECT COUNT(*) AS total_ios_users
FROM users_data
WHERE uses_ios_app = 1;
8. Calculate the Total Number of Buyers for Each Country
sql
SELECT country, COUNT(*) AS total_buyers
FROM users_data
WHERE products_bought > 0
GROUP BY country
ORDER BY total_buyers DESC;
9. Calculate the Total Number of Sellers for Each Country
sql
SELECT country, COUNT(*) AS total_sellers
FROM users_data
WHERE products_sold > 0
GROUP BY country
ORDER BY total_sellers ASC;
10. Top 10 Countries with Maximum Product Pass Rate
Assuming product_pass_rate is a column:
sql
SELECT country, AVG(product_pass_rate) AS avg_pass_rate
FROM users_data
GROUP BY country
ORDER BY avg_pass_rate DESC
LIMIT 10;
11. Number of Users on E-commerce Platform for Different Language Choices
sql
SELECT language, COUNT(*) AS total_users
FROM users_data
GROUP BY language;
12. Female Users' Choice of Putting Products in Wishlist or Liking Socially
Assuming there are columns wishlist_count and social_likes:
sql
SELECT 'Wishlist' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'female' AND wishlist_count > 0
UNION
SELECT 'Social Likes' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'female' AND social_likes > 0;
13. Male Users' Choice of Being a Seller or Buyer
sql
SELECT 'Seller' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'male' AND products_sold > 0
UNION
SELECT 'Buyer' AS choice, COUNT(*) AS total
FROM users_data
WHERE gender = 'male' AND products_bought > 0;
14. Country with Maximum Number of Buyers
sql
SELECT country
FROM users_data
WHERE products_bought > 0
GROUP BY country
ORDER BY COUNT(*) DESC
LIMIT 1;
15. List of 10 Countries with Zero Sellers
sql
SELECT country
FROM users_data
GROUP BY country
HAVING SUM(products_sold) = 0
LIMIT 10;
16. Top 110 Users Who Have Used E-commerce Platform Recently
Assuming last_login is a date or datetime column:
sql
SELECT * FROM users_data
ORDER BY last_login DESC
LIMIT 110;
17. Number of Female Users Who Have Not Logged in Since Last 100 Days
sql
SELECT COUNT(*) AS total_female_inactive
FROM users_data
WHERE gender = 'female' AND DATEDIFF(CURDATE(), last_login) > 100;
18. Number of Female Users in Each Country on E-commerce Platform
sql
SELECT country, COUNT(*) AS total_female_users
FROM users_data
WHERE gender = 'female'
GROUP BY country;
19. Number of Male Users in Each Country on E-commerce Platform
sql
SELECT country, COUNT(*) AS total_male_users
FROM users_data
WHERE gender = 'male'
GROUP BY country;
20. Average Number of Products Sold and Bought by Male Users for Each Country
sql
SELECT country, AVG(products_sold) AS avg_sold, AVG(products_bought) AS avg_bought
FROM users_data
WHERE gender = 'male'
GROUP BY country;
These queries will help you analyze the data in the users_data table effectively.