SQL_project_.sql

select count( * ) from sales_retail 

-- Data Cleaning 
select * from sales_retail 
where sale_date is null
or customer_id is null
or gender is null
or category is null
or quantiy is null
or price_per_unit is null
or cogs is null
or transactions_id is null
or total_sale is null

delete from sales_retail where sale_date is null
or customer_id is null
or gender is null
or category is null
or quantiy is null
or price_per_unit is null
or cogs is null
or transactions_id is null
or total_sale is null

-- totals sale we have
select count( * )from sales_retail 

-- total customer unique
select count( DISTINCT customer_id )from sales_retail 

--total catergory unique
select count( DISTINCT category )from sales_retail 

-- Data analysis and Bussiness analysis problems.
-- write a sql query to retrive all columns fro sales made on '2022-11-05
select * 
from sales_retail 
where sale_date = '2022-11-05'

-- write a sql calculate the total sale for each category
select category,
sum(total_sale)
from sales_retail
group by category

-- write a sql query to find the average age of customers who purchased items from the "beauty" category
select 
avg(age) as AVERAGE_AGE
from sales_retail 
where category= 'Beauty'

-- Write a sql query to final all transactiosn where the total_sales is greater than 1000
select * from sales_retail where total_sale > 1000

--Write a sql query to find the total number of transactions made by each gender in each category
select category,
gender,
count(*) as total_trans
from sales_retail
group by category,gender

-- write a sql query to fing the top 5 customers based on the highest total sales
select customer_id,
sum(total_sale) as total_sales,
from retail_sales 
group by customer_id
order by total_sales

-- write a sql query to find the number of unique customers who purchased item from each category.

select category,
count(distinct customer_id) as count_unique customer
from retail_sales group by category

-- write a sql query to create each shift and numger of orders
select * ,case
when datepart(hour,sale_time) < 12 then 'morning'
when datepart(hour,sale_time) Between 12 and 17 then 'afternoon'
else 'evening'
End as shift
from retail_sales