Sales Order Performance & Revenue Impact Analysis.sql

create database project;
use project;

-- Count records --
SELECT COUNT(*) FROM prd_info;
SELECT COUNT(*) FROM sales_details;

-- Preview data --
SELECT * FROM prd_info LIMIT 10;
SELECT * FROM sales_details LIMIT 10;

-- Check NULL values--
SELECT COUNT(*) 
FROM prd_info
WHERE prd_id IS NULL;

SELECT COUNT(*) 
FROM sales_details
WHERE sls_ord_num IS NULL;

-- Check duplicate products --
SELECT prd_id, COUNT(*) 
FROM prd_info
GROUP BY prd_id
HAVING COUNT(*) > 1;

-- Total records & basic stats --
SELECT 
SUM(sls_sales) AS total_sales,
SUM(sls_quantity) AS total_units,
ROUND(AVG(sls_sales),2) AS avg_order_value
FROM sales_details;

-- Monthly sales trend --
SELECT 
DATE_FORMAT(sls_order_dt, '%Y-%m') AS month,
SUM(sls_sales) AS monthly_sales
FROM sales_details
GROUP BY month
ORDER BY month;

-- Month-over-month growth -- 
WITH monthly_sales AS (
SELECT 
DATE_FORMAT(sls_order_dt, '%Y-%m') AS month,
SUM(sls_sales) AS sales
FROM sales_details
GROUP BY month
)
SELECT month, sales, sales - LAG(sales) OVER (ORDER BY month) AS mom_growth
FROM monthly_sales;

-- Top 10 selling products --
SELECT 
sls_prd_key,
SUM(sls_sales) AS total_sales
FROM sales_details
GROUP BY sls_prd_key
ORDER BY total_sales DESC
LIMIT 10;

-- Low-performing products --
SELECT 
sls_prd_key,
SUM(sls_sales) AS total_sales
FROM sales_details
GROUP BY sls_prd_key
ORDER BY total_sales ASC
LIMIT 10;

-- Shipping delay analysis --
SELECT 
sls_ord_num,
DATEDIFF(sls_ship_dt, sls_order_dt) AS ship_delay_days
FROM sales_details
WHERE DATEDIFF(sls_ship_dt, sls_order_dt) > 5
ORDER BY ship_delay_days DESC;

-- Average delivery time --
SELECT 
ROUND(AVG(DATEDIFF(sls_ship_dt, sls_order_dt)),2) AS avg_ship_days
FROM sales_details;

-- High-value orders --
SELECT *
FROM sales_details
WHERE sls_sales > (
SELECT AVG(sls_sales) FROM sales_details
)
ORDER BY sls_sales DESC;

-- Revenue impact of delayed orders --
SELECT 
CASE 
WHEN sls_ship_dt <= sls_due_dt THEN 'On Time'
ELSE 'Delayed'
END AS delivery_status,
SUM(sls_sales) AS total_sales
FROM sales_details
GROUP BY delivery_status;