Amazon Business Problem Solved Query.sql

-- EDA

SELECT * FROM category ;
SELECT * FROM customers ; 
SELECT * FROM inventory ;
SELECT * FROM order_items ;
SELECT * FROM orders ;
SELECT * FROM payments ; 
SELECT * FROM products ; 
SELECT * FROM sellers ; 
SELECT * FROM shippings ;

SELECT 
	DISTINCT payment_status
FROM payments ;

SELECT * 
FROM shippings
WHERE return_date IS NOT NULL ;

SELECT * 
FROM orders
WHERE order_id = 1081 ;

SELECT * 
FROM  payments
WHERE order_id = 1081 ;

SELECT * 
FROM shippings
WHERE return_date IS NULL ;

------------------------------------------------------------
-- Business problem 
-- Advanced Analysis
------------------------------------------------------------
/* 
1. Top Selling Products
Query  the top 10 products by total sales value.
Challenge: Include product name, total quantity sold, and total sales value.
*/

-- Create New Column 
ALTER TABLE order_items
ADD COLUMN total_sales FLOAT;

-- Update Price qty * price per unit

UPDATE order_items
SET total_sales = quantity * price_per_unit;
SELECT * FROM order_items ;


SELECT 
	p.product_id,
	p.product_name,
	SUM(total_sales) AS Total_sales,
	COUNT(o.order_id) AS Total_orders
FROM orders AS o
JOIN 
	order_items AS oi
  ON 
		o.order_id = oi.order_id
JOIN 
	products AS p
  ON 
	oi.product_id = p.product_id 
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 10 ;

/*
2. Revenue by Category
Calculate total revenue generated by each product category.
Challenge: Include the percentage contribution of each category to total revenue.
*/

SELECT 
	p.category_id,
	c.category_name,
	SUM(oi.total_sales) AS Toral_sales,
	SUM(oi.total_sales) /
						(SELECT SUM(total_sales) FROM order_items) * 100
		AS contribution
FROM order_items AS oi
JOIN 
	products AS p
 ON 
	oi.product_id = p.product_id 
LEFT JOIN 
	category AS c
 ON 
	p.category_id = c.category_id
GROUP BY 1,2
ORDER BY 3 DESC ;

/*
3. Average Order Value (AOV)
Compute the average order value for each customer.
Challenge: Include only customers with more than 4 orders.
*/

SELECT 
	c.customer_id,
	CONCAT(c.first_name ,' ', c.last_name),
	SUM(total_sales) / COUNT(o.order_id) AS aov ,
	COUNT(o.order_id) AS total_orders
FROM orders AS o
JOIN 
	customers AS c
 ON 
	o.customer_id = c.customer_id
JOIN 
	order_items AS oi
 ON 
	oi.order_id = o.order_id
GROUP BY 1,2
HAVING COUNT(o.order_id) > 4 ;

/* 
4. Monthly Sales Trend
Query monthly total sales over the past year.
Challenge: Display the sales trend, grouping by month, return current_month sale, last month sale!
*/

SELECT
	year,
	month,
	total_sales AS current_month_sales,
	LAG(total_sales, 1) OVER (ORDER BY year,month) AS last_month_sales
FROM
(
SELECT 
	EXTRACT(MONTH FROM o.order_date) AS month,
	EXTRACT (YEAR FROM o.order_date) AS year,
	ROUND(SUM(oi.total_sales:: numeric),2) AS total_sales
	
FROM orders AS o
JOIN 
	order_items AS oi
 ON
	o.order_id = oi.order_id
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY 1,2
ORDER BY 1 ASC
)t1 ;

/* 
5. Customers with No Purchases
Find customers who have registered but never placed an order.
Challenge: List customer details and the time since their registration.
*/

--Approach 1
SELECT *
FROM customers
WHERE customer_id NOT IN (SELECT 
							DISTINCT customer_id 
							FROM orders) ;

--Approach 2
SELECT *
FROM customers AS c
LEFT JOIN 
	orders AS o
 ON
	c.customer_id = o.customer_id
WHERE o.customer_id IS NULL ;


/*
6.Least-Selling Categories by State
Identify the least-selling product category for each state.
Challenge: Include the total sales for that category within each state.
*/

WITH ranking_table
AS
(
	SELECT 
		cust.state,
		c.category_name,
		SUM(oi.total_sales) AS total_sales,
		RANK()OVER(PARTITION BY cust.state ORDER BY SUM(oi.total_sales) DESC) AS rank
	FROM customers AS cust
	JOIN 
		orders AS o
	 ON 
		cust.customer_id = o.customer_id
	JOIN 
		order_items AS oi
	 ON 
		o.order_id = oi.order_id
	JOIN 
		products AS p
	 ON
		oi.product_id = p.product_id
	JOIN 
		category AS c
	 ON 
		p.category_id = c.category_id
	GROUP BY 1,2
)
SELECT *
FROM ranking_table
WHERE rank = 1 ;

/*
7. Customer Lifetime Value (CLTV)
Calculate the total value of orders placed by each customer over their lifetime.
Challenge: Rank customers based on their CLTV.
*/

SELECT 
	c.customer_id,
	CONCAT(c.first_name ,' ', c.last_name),
	SUM(total_sales) AS CLTV,
	DENSE_RANK() OVER (ORDER BY SUM(total_sales) DESC) AS cx_ranking
FROM orders AS o
JOIN 
	customers AS c
 ON 
	o.customer_id = c.customer_id
JOIN 
	order_items AS oi
 ON 
	oi.order_id = o.order_id
GROUP BY 1,2 ;

/*
Inventory Stock Alerts
Query products with stock levels below a certain threshold (e.g., less than 10 units).
Challenge: Include last restock date and warehouse information.
*/

SELECT 
	i.inventory_id,
	p.product_name,
	i.stock AS current_stock_left,
	i.last_stock_date,
	i.warehouse_id
FROM inventory AS i
JOIN
	products AS p
 ON 
	i.product_id = p.product_id
WHERE stock < 10 ;


/*
9. Shipping Delays
Identify orders where the shipping date is later than 3 days after the order date.
Challenge: Include customer, order details, and delivery provider.
*/

SELECT 
	c.* ,
	o.* ,
	s.shipping_provider,
	 s.shipping_date - o.order_date AS Days_took_to_ship
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
JOIN shippings AS s
ON o.order_id = s.order_id
WHERE s.shipping_date - o.order_date > 3 ;


/*
10. Payment Success Rate
Calculate the percentage of successful payments across all orders.
Challenge: Include breakdowns by payment status (e.g., failed, pending).
*/

SELECT 
	p.payment_status,
	COUNT(*)AS total_cnt,
	COUNT(*) / (SELECT COUNT(*)FROM payments):: numeric * 100
FROM orders AS o
JOIN payments AS p
ON o.order_id = p.order_id
GROUP BY 1 ;

/*
11. Top Performing Sellers
Find the top 5 sellers based on total sales value.
Challenge: Include both successful and failed orders, and display their percentage of successful orders.
*/

WITH top_sellers
AS
(
	SELECT 
		s.seller_id,
		s.name,
		SUM(total_sales) AS total_sales
	FROM orders AS o
	JOIN sellers AS s
	ON o.seller_id = s.seller_id
	JOIN order_items AS oi
	ON oi.order_id = o.order_id
	GROUP BY 1,2
	ORDER BY 3 DESC
	LIMIT 5
),

sellers_reports
AS
(
SELECT 
	o.seller_id,
	ts.name,
	o.order_status,
	COUNT(*) AS total_orders
FROM orders AS o
JOIN top_sellerS AS ts
ON o.seller_id = ts.seller_id
WHERE o.order_status NOT IN ('InProgress','Returned')
GROUP BY 1,2,3
)
SELECT 
	seller_id,
	name,
	SUM(CASE WHEN order_status = 'Delivered' THEN total_orders ELSE 0 END) AS Completed_orders,
	SUM(CASE WHEN order_status = 'Cancelled' THEN total_orders ELSE 0 END) AS Cancelled_orders,
	SUM(total_orders),
	SUM(CASE WHEN order_status = 'Delivered' THEN total_orders ELSE 0 END) / 
	SUM(total_orders) :: numeric * 100 AS successful_orders_pct
FROM sellers_reports
GROUP BY 1,2 ;


/*
12. Product Profit Margin
Calculate the profit margin for each product (difference between price and cost of goods sold).
Challenge: Rank products by their profit margin, showing highest to lowest.
*/

SELECT 
	product_id,
	product_name,
	profit_margin,
	DENSE_RANK() OVER (ORDER BY profit_margin DESC ) AS product_ranking
FROM 
	(
	SELECT
		p.product_id,
		p.product_name,
		SUM(total_sales - (p.cogs * oi.quantity)) AS profit,
		SUM(total_sales - (p.cogs * oi.quantity)) / SUM(total_sales) * 100  AS profit_margin
	FROM products AS p
	JOIN order_items AS oi
	ON p.product_id = oi.product_id
	GROUP BY 1,2
	)t1 ;


/*
13. Most Returned Products
Query the top 10 products by the number of returns.
Challenge: Display the return rate as a percentage of total units sold for each product.
*/

SELECT 
	p.product_id,
	p.product_name,
	COUNT(*) AS total_unit_sold,
	SUM(CASE WHEN o.order_status = 'Returned' THEN 1 ELSE 0 END) AS total_returns,
	SUM(CASE WHEN o.order_status = 'Returned' THEN 1 ELSE 0 END):: numeric / COUNT(*):: numeric * 100 AS return_pct
FROM products AS p
JOIN order_items AS oi
ON p.product_id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.order_id
GROUP BY 1,2
ORDER BY 5 DESC ;

/*
14. Inactive Sellers
Identify sellers who haven't made any sales in the last 6 months.
Challenge: Show the last sale date and total sales from those sellers.
*/

WITH cte1 -- AS these seller has not done any sale in last 6 month
AS
(
	SELECT *
	FROM sellers
	WHERE seller_id NOT IN (SELECT seller_id FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '6 month')
)
SELECT 
	o.seller_id,
	MAX(o.order_date) as last_sale_date,
	MAX(total_sales) AS last_sales_amount
FROM orders AS o
JOIN 
	cte1 
 ON 
	o.seller_id = cte1.seller_id
JOIN 
	order_items AS oi
 ON
	o.order_id = oi.order_id
GROUP BY 1	;


/*
15. IDENTITY customers into returning or new
if the customer has done more than 5 return categorize them as returning otherwise new
Challenge: List customers id, name, total orders, total returns
*/

SELECT 
	full_name AS customer,
	total_orders,
	total_returns,
	CASE 
		WHEN total_returns > 5 THEN 'returning_customers' ELSE 'new'
	END
		AS cx_category
FROM
	
	(
		SELECT 
			CONCAT(c.first_name ,' ', c.last_name) AS full_name,
			COUNT(o.order_id) AS total_orders,
			SUM(CASE WHEN o.order_status = 'Returned' THEN 1 ELSE 0 END) AS total_returns
		FROM orders AS o
		JOIN 
			customers AS c
		 ON 
			o.customer_id = c.customer_id
		JOIN 
			order_items AS oi
		 ON 
			oi.order_id = o.order_id
		GROUP BY 1 
	) ;