RETAIL_SALES_ANALYSIS_sql_query.sql

-- PROJECT -> RETAIL SALES ANALYSIS 
CREATE DATABASE sql_project1;

USE sql_project1;

--  CREATING TABLE
DROP TABLE IF EXISTS retail_sales;
CREATE TABLE retail_sales (
	transactions_id	int primary key,
    sale_date	date,
    sale_time	time,
    customer_id	int,
    gender	varchar(8),
    age	int(2),
    category	varchar(15),
    quantiy	int,
    price_per_unit float,	
    cogs	float,
    total_sale float
);

select * from retail_sales limit 10;

select count(*) from retail_sales;

select * from retail_sales limit 500;


 -- CHECKING NULL VALUES - DATA CLEANING 
select * from retail_sales
where 
transactions_id is null
or
sale_date is null
or
sale_time is null
or
customer_id is null
or
gender is null
or
age is null
or
category is null
or 
quantiy is null
or
price_per_unit is null
or
cogs is null
or total_sale is null;


-- DATA EXPLORATION
--  How many sales do we have? 
SELECT COUNT(TRANSACTIONS_ID) AS TOTAL_SALES FROM RETAIL_SALES;

-- How many unique customers do we have?
select count(distinct customer_id) from retail_sales;



-- DATA ANALYSIS FOR BUSINESS KEY PROBLEMS
-- My Analysis & Findings
-- Q.1 Write a SQL query to retrieve all columns for sales made on '2022-11-05
-- Q.2 Write a SQL query to retrieve all transactions where the category is 'Clothing' and the quantity sold is more than 4 in the month of Nov-2022
-- Q.3 Write a SQL query to calculate the total sales (total_sale) for each category.
-- Q.4 Write a SQL query to find the average age of customers who purchased items from the 'Beauty' category.
-- Q.5 Write a SQL query to find all transactions where the total_sale is greater than 1000.
-- Q.6 Write a SQL query to find the total number of transactions (transaction_id) made by each gender in each category.
-- Q.7 Write a SQL query to calculate the average sale for each month. Find out best selling month in each year
-- Q.8 Write a SQL query to find the top 5 customers based on the highest total sales 
-- Q.9 Write a SQL query to find the number of unique customers who purchased items from each category.
-- Q.10 Write a SQL query to create each shift and number of orders (Example Morning <=12, Afternoon Between 12 & 17, Evening >17)



-- SOLUTION Q1
SELECT * FROM retail_sales where sale_date = '2022-11-05';

-- SOLUTION Q2
SELECT * from retail_sales where category = 'Clothing' and quantiy >= 4 and date_format(sale_date, '%Y-%m') = '2022-11';

-- SOLUTION Q3
SELECT category,sum(total_sale) as net_sale, count(*) as total_orders from retail_sales group by category;

-- SOLUTION Q4
SELECT avg(age) FROM retail_sales where category = 'Beauty';

-- SOLUTION Q5
SELECT * from retail_sales where total_sale >1000;

-- SOLUTION Q6
SELECT category,gender, count(transactions_id) from retail_sales group by category,gender order by category asc;

-- SOLUTION Q7
SELECT Year(sale_date) as Year,Month(sale_date) as Month, avg(total_sale) as Average_Sale from retail_sales group by Year,Month;

-- SOLUTION Q8
SELECT customer_id, SUM(total_sale) AS total_sales FROM retail_sales GROUP BY customer_id ORDER BY total_sales DESC LIMIT 5;

-- SOLUTION Q9
select category, count(distinct(customer_id)) as No_of_customers from retail_sales group by category;


-- SOLUTION Q10
SELECT 
    CASE
        WHEN HOUR(sale_time) < 12 THEN 'Morning'
        WHEN HOUR(sale_time) BETWEEN 12 AND 17 THEN 'Afternoon'
        ELSE 'Evening'
    END AS shift,
     COUNT(transactions_id) AS number_of_orders
FROM retail_sales
GROUP BY shift;