Walmart_Sales_Data_Analysis(SQL).sql

use walmartsd
SELECT *FROM walmartsalesdata;


#Generic Questions:
#1. How many unique cities does the data have?
SELECT City, COUNT(*) FROM walmartsalesdata  GROUP BY City;

#In which city is each branch?
SELECT branch, city, COUNT(*) AS count FROM walmartsalesdata GROUP BY branch, city;


#Product Analysis:
#1. How many unique product lines does the data have?
SELECT `Product line`, COUNT(*) AS count FROM walmartsalesdata GROUP BY `Product line`;

#2. What is the most common payment method?
SELECT Payment, COUNT(*) AS count 
FROM walmartsalesdata 
GROUP BY Payment
ORDER BY count DESC 
LIMIT 1;

#3. What is the most selling product line?
SELECT `Product line`, SUM(quantity) AS total_sold 
FROM walmartsalesdata 
GROUP BY `Product line` 
ORDER BY total_sold DESC 
LIMIT 1;

#4. What is the total revenue by month?
SELECT MONTH(date) AS month, SUM(total) AS Total_Revenue 
FROM walmartsalesdata 
GROUP BY MONTH(date) 
ORDER BY month;

#5. What month had the largest COGS?
SELECT MONTH(date) AS month, SUM(cogs) AS total_cogs 
FROM walmartsalesdata 
GROUP BY MONTH(date) 
ORDER BY total_cogs DESC 
LIMIT 1;

#6. What product line had the largest revenue?
SELECT `Product line`, SUM(total) AS total_revenue 
FROM walmartsalesdata 
GROUP BY `Product line` 
ORDER BY total_revenue DESC 
LIMIT 1;

#7. What is the city with the largest revenue?
SELECT city, SUM(total) AS total_revenue 
FROM walmartsalesdata 
GROUP BY city 
ORDER BY total_revenue DESC 
LIMIT 1;

#8. What product line had the largest VAT?
SELECT `Product line`, SUM(`Tax 5%`) AS total_vat 
FROM walmartsalesdata 
GROUP BY `Product line`
ORDER BY total_vat DESC 
LIMIT 1;

#9. Fetch each product line and add a column to classify them as "Good" or "Bad" based on average sales.
SELECT `Product line`, 
       CASE 
           WHEN AVG(quantity) > (SELECT AVG(quantity) FROM walmartsalesdata) THEN 'Good' 
           ELSE 'Bad' 
       END AS category
FROM walmartsalesdata 
GROUP BY `Product line`;

#10. Which branch sold more products than the average product sold?
SELECT branch, SUM(quantity) AS total_sold 
FROM walmartsalesdata 
GROUP BY branch 
HAVING total_sold > (SELECT AVG(quantity) FROM walmartsalesdata);

#11. What is the most common product line by gender?
SELECT gender, `Product line`, COUNT(*) AS total_sold 
FROM walmartsalesdata 
GROUP BY gender, `Product line` 
ORDER BY gender, total_sold DESC;

#12. What is the average rating of each product line?
SELECT `Product line`, AVG(rating) AS avg_rating 
FROM walmartsalesdata 
GROUP BY `Product line` 
ORDER BY avg_rating DESC;


#Sales Analysis
#1. Number of sales made in each time of the day per weekday
SELECT DAYNAME(date) AS weekday, Time, COUNT(*) AS sales_count 
FROM walmartsalesdata 
GROUP BY weekday, Time 
ORDER BY weekday;

#2. Which customer type brings the most revenue?
SELECT `Customer type` , SUM(total) AS total_revenue 
FROM walmartsalesdata 
GROUP BY `Customer type`
ORDER BY total_revenue DESC 
LIMIT 1;

#3. Which city has the largest tax percentage (VAT)?
SELECT city, AVG(`Tax 5%`) AS avg_tax 
FROM walmartsalesdata 
GROUP BY city 
ORDER BY avg_tax DESC 
LIMIT 1;

#4. Which customer type pays the most in VAT?
SELECT `Customer type`, SUM(`Tax 5%`) AS total_vat 
FROM walmartsalesdata 
GROUP BY `Customer type` 
ORDER BY total_vat DESC 
LIMIT 1;


#Customer Analysis
#1. How many unique customer types does the data have?
SELECT customer_type, COUNT(*) AS count FROM sales GROUP BY customer_type;

#2. How many unique payment methods does the data have?
SELECT payment, COUNT(*) AS count FROM sales GROUP BY payment;

#3. What is the most common customer type?
SELECT customer_type, COUNT(*) AS count 
FROM sales 
GROUP BY customer_type 
ORDER BY count DESC 
LIMIT 1;

#4. Which customer type buys the most?
SELECT customer_type, SUM(quantity) AS total_bought 
FROM sales 
GROUP BY customer_type 
ORDER BY total_bought DESC 
LIMIT 1;

#5. What is the gender of most of the customers?
SELECT gender, COUNT(*) AS count 
FROM sales 
GROUP BY gender 
ORDER BY count DESC 
LIMIT 1;

#6. What is the gender distribution per branch?
SELECT branch, gender, COUNT(*) AS count 
FROM sales 
GROUP BY branch, gender 
ORDER BY branch, count DESC;

#7. Which time of the day do customers give most ratings?
SELECT time_of_day, AVG(rating) AS avg_rating 
FROM sales 
GROUP BY time_of_day 
ORDER BY avg_rating DESC 
LIMIT 1;

#8. Which time of the day do customers give most ratings per branch?
SELECT branch, time_of_day, AVG(rating) AS avg_rating 
FROM sales 
GROUP BY branch, time_of_day 
ORDER BY branch, avg_rating DESC;

#9. Which day of the week has the best average ratings?
SELECT DAYNAME(date) AS weekday, AVG(rating) AS avg_rating 
FROM sales 
GROUP BY weekday 
ORDER BY avg_rating DESC 
LIMIT 1;

#10. Which day of the week has the best average ratings per branch?
SELECT branch, DAYNAME(date) AS weekday, AVG(rating) AS avg_rating 
FROM sales 
GROUP BY branch, weekday 
ORDER BY branch, avg_rating DESC;