🛒 Retail Sales SQL Analysis

This project contains SQL queries for Retail Sales Analysis, including database setup, data cleaning, exploration, and solving business-related queries.

-- Database Setup
create database sql_retail_analysis;
use sql_retail_analysis;

create table retail_sales(
    transactions_id int primary key,
    sale_date date,
    sale_time time,
    customer_id int,
    gender varchar(15),
    age int,
    category varchar(30),
    quantity int,
    price_per_unit float,
    cogs float,
    total_sale float
);

-- Basic Queries
select * from retail_sales;
drop table retail_sales;
select count(*) from retail_sales;
select * from retail_sales limit 10;

-- 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 quantity is null
 or price_per_unit is null
 or cogs is null
 or total_sale is null;

SET SQL_SAFE_UPDATES = 0;

delete 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 quantity is null
 or price_per_unit is null
 or cogs is null
 or total_sale is null;

-- Data Exploration
select count(*) from retail_sales;
select count(distinct customer_id) from retail_sales;
select distinct category from retail_sales;

-- Business Problem Queries

-- Q1: Sales made on 2022-11-05
select * from retail_sales 
where sale_date = '2022-11-05';

-- Q2: Clothing transactions with quantity > 10 in Nov 2022
select * from retail_sales 
where category='Clothing' and quantity>=4 
and sale_date between '2022-11-01' and '2022-11-30';

-- Q3: Total Sales per Category
select category, sum(total_sale) as total_sales, count(*) as total_orders
from retail_sales
group by 1;

-- Q4: Average Age of Customers (Beauty category)
select round(avg(age),2) as average_age 
from retail_sales
where category='Beauty';

-- Q5: Transactions with Total Sale > 1000
select * from retail_sales
where total_sale > 1000;

-- Q6: Number of Transactions by Gender & Category
select category, gender, count(transactions_id) as total_transactions
from retail_sales
group by 1,2
order by 1;

-- Q7: Average Sale per Month & Best Selling Month per Year
select extract(year from sale_date), extract(month from sale_date), 
       avg(total_sale) as avg_sale,
       rank() over(partition by extract(year from sale_date) order by avg(total_sale) desc) as rank_in_year
from retail_sales
group by 1,2
order by 1,3 desc;

-- Q8: Top 5 Customers by Total Sales
select customer_id, sum(total_sale) as total_sales
from retail_sales
group by customer_id
order by sum(total_sale) desc 
limit 5;

-- Q9: Unique Customers per Category
select category, count(distinct customer_id) as unique_customers
from retail_sales
group by 1;

-- Q10: Orders by Shift (Morning, Afternoon, Evening)
with shift_orders as (
    select *,
    case
        when extract(hour from sale_time) < 12 then "Morning"
        when extract(hour from sale_time) between 12 and 17 then "Afternoon"
        else "Evening"
    end as Shift
    from retail_sales
)
select shift, count(transactions_id) as total_orders
from shift_orders
group by shift;