Project 1
CREATE SCHEMA ecommerce;
CREATE TABLE ecommerce.users_data (
user_id INT,
username VARCHAR(50),
email VARCHAR(100),
-- Add more columns as per your CSV file structure
);
LOAD DATA INFILE '/path/to/your/users_data.csv'
INTO TABLE ecommerce.users_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
DESCRIBE ecommerce.users_data;
SELECT * FROM ecommerce.users_data LIMIT 100;
SELECT COUNT(DISTINCT country) AS distinct_countries, COUNT(DISTINCT language) AS distinct_languages
FROM ecommerce.users_data;
SELECT COUNT(DISTINCT country) AS distinct_countries, COUNT(DISTINCT language) AS distinct_languages
FROM ecommerce.users_data;
SELECT gender, MAX(followers) AS max_followers
FROM ecommerce.users_data
GROUP BY gender
HAVING MAX(followers) >= ALL (SELECT MAX(followers) FROM ecommerce.users_data GROUP BY gender);
SELECT COUNT(*) AS total_with_profile_picture
FROM ecommerce.users_data
WHERE has_profile_picture = 1;
SELECT COUNT(*) AS total_android_users
FROM ecommerce.users_data
WHERE uses_android_app = 1;
SELECT COUNT(*) AS total_ecommerce_users
FROM ecommerce.users_data
WHERE uses_ecommerce_app = 1;
SELECT COUNT(*) AS total_ios_users
FROM ecommerce.users_data
WHERE uses_ios_app = 1;
SELECT c.country,
COUNT(DISTINCT p.user_id) AS total_buyers
FROM ecommerce.users_data u
INNER JOIN ecommerce.purchases p ON u.user_id = p.user_id
INNER JOIN ecommerce.countries c ON u.country_id = c.country_id
GROUP BY c.country
HAVING total_buyers > 0
ORDER BY total_buyers DESC;
SELECT c.country,
COUNT(DISTINCT s.user_id) AS total_sellers
FROM ecommerce.users_data u
INNER JOIN ecommerce.sales s ON u.user_id = s.user_id
INNER JOIN ecommerce.countries c ON u.country_id = c.country_id
GROUP BY c.country
HAVING total_sellers > 0
ORDER BY total_sellers ASC;
SELECT c.country,
SUM(CASE WHEN p.passed = 1 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate
FROM products p
INNER JOIN countries c ON p.country_id = c.country_id
GROUP BY c.country
ORDER BY pass_rate DESC
LIMIT 10;
SELECT language, COUNT(*) AS user_count
FROM ecommerce.users_data
GROUP BY language;
-- Query to select female users who put products in their wishlist
SELECT user_id, 'Wishlist' AS action
FROM ecommerce.wishlist
WHERE user_id IN (
SELECT user_id
FROM ecommerce.users_data
WHERE gender = 'Female'
)
UNION
-- Query to select female users who like products socially
SELECT user_id, 'Like' AS action
FROM ecommerce.social_likes
WHERE user_id IN (
SELECT user_id
FROM ecommerce.users_data
WHERE gender = 'Female'
);