1.

SQL Table Creation Command

CREATE TABLE retail_sales (

transactions_id INT PRIMARY KEY,
sale_date DATE,
sale_time TIME,
customer_id INT,
gender VARCHAR(10),
age INT,
category VARCHAR(50),
quantity INT,
price_per_unit DECIMAL(10,2),
cogs DECIMAL(10,2),
total_sale DECIMAL(10,2)
);

2. Insert Sample Row

INSERT INTO retail_sales (

transactions_id, sale_date, sale_time, customer_id,
gender, age, category, quantity,
price_per_unit, cogs, total_sale
) VALUES (
180, '2022-11-05', '10:47:00', 117,
'Male', 41, 'Clothing', 3,
300.00, 129.00, 900.00
);

3. Example SQL Queries

a. Monthly Revenue Trend

SELECT
DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
 SUM(total_sale) AS monthly_revenue
FROM retail_sales
GROUP BY sale_month
ORDER BY sale_month;

b. Top Categories by Revenue

SELECT
category,
SUM(total_sale) AS revenue
FROM retail_sales
GROUP BY category
ORDER BY revenue DESC
LIMIT 5;

c. Top Customers by Total Sale

SELECT
customer_id,
SUM(total_sale) AS total_spent
FROM retail_sales
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

a. Monthly Revenue Trend

Month Total
Revenue

2022-01 ₹22,635

2022-02 ₹16,110

2022-03 ₹24,505

2022-04 ₹28,705

... ...

2023-11 ₹57,135
2023-12 ₹69,145

Revenue peaks in December 2023, suggesting strong end-of-year sales.

b. Top Product Categories by Revenue

Category Revenu
e

Electronics ₹313,81
0

Clothing ₹311,070

Beauty ₹286,84
0

Electronics narrowly leads over Clothing.

c. Top 10 Customers by Total Spending

Customer ID Total Spent

3 ₹38,440

1 ₹30,750

5 ₹30,405

2 ₹25,295

4 ₹23,580

... ...

84 ₹11,730

These top 5 customers are ideal targets for loyalty programs or premium offers.