-- Profitability Analysis
-- Solve a business problem using SQL. 
-- In the first problem, you will identify the profitable product categories in the 'market star' schema

-- Problem Statement:
-- Identify the sustainable (profitable) product categories so that the growth team can capitalise on them to increase sales.
-- 1. Profit based on Product Category
SELECT Product_Category,
       SUM(profit) as Profits
FROM market_fact_full as m
INNER JOIN prod_dimen as p
ON m.prod_id = p.prod_id
GROUP BY Product_Category
ORDER BY Profits DESC;

-- 2. Profit based on Product Subcategory
SELECT Product_Category,
       Product_Sub_Category,
       SUM(profit) as Profits
FROM market_fact_full as m
INNER JOIN prod_dimen as p
ON m.prod_id = p.prod_id
GROUP BY Product_Category,Product_Sub_Category
ORDER BY Profits DESC;
-- Checking to see if we have distinct order ids
SELECT COUNT(*) as rec_count,
       COUNT(DISTINCT ord_id) as ord_id_count,
       COUNT(DISTINCT order_number) as ord_number_count
FROM orders_dimen;
-- We see a difference in count
-- Checking for orderid with orders > 1
SELECT order_number,
	   COUNT(ord_id)
FROM orders_dimen
GROUP BY order_number
HAVING COUNT(ord_id)>1;

-- Checking the records where order_ids are > 1
SELECT *
FROM orders_dimen
WHERE order_number IN
(
SELECT order_number
FROM orders_dimen
GROUP BY order_number
HAVING COUNT(ord_id)>1
);
-- 3. Average Profit per order
SELECT Product_category,
       SUM(m.profit) as Profits,
       ROUND((SUM(Profit)/COUNT(o.order_number)),2) AS Avg_Profits
FROM prod_dimen as p
INNER JOIN
market_fact_full as m
using(prod_id)
INNER JOIN orders_dimen as o
using(ord_id)
GROUP BY p.product_category
ORDER BY p.Product_Category,
         SUM(m.profit);

-- 4. Average Profit percentage per product_category

WITH Avg_details AS
(
SELECT Product_category,
       SUM(m.profit) as Profits,
       ROUND((SUM(m.Profit)/COUNT(o.order_number)),2) AS Avg_Profits,
       ROUND((SUM(m.Sales)/COUNT(o.order_number)),2) AS Avg_Sales
FROM prod_dimen as p
INNER JOIN
market_fact_full as m
using(prod_id)
INNER JOIN orders_dimen as o
using(ord_id)
GROUP BY p.product_category
ORDER BY p.Product_Category,
         SUM(m.profit))
SELECT Product_category,
       Avg_Profits/Avg_Sales as Profit_percent
FROM Avg_details
ORDER BY Profit_percent;

-- It is observed that the average profit percentage per order for furniture products is quite low (2.27%) 
-- compared to the other product categories. 
-- Such low values of the average profit and profit percentage per order for furniture show that these 
-- products are not doing well. Their sale should ideally be stopped or the company should come up with a 
-- robust plan to deal with this issue.