1_business_problems.sql

----------------------------------------
----BUSINESS PROBLEMS---
----------------------------------------

/*
1. TOP SELLING PRODUCTS
Query the top 10 products by total sales value.
Challenge: Include product name, total quantity sold, and total sales value.

In the Amazon e-commerce dataset, the order_items table represents the individual products included 
in each order — it's the bridge between orders and products
Steps:
1- Create a new column called total_sales and multiply quantity and price_per_unit to get 
the total_sales per order_item_id(representing a single product)
*/
---------------------------------------------


-- Step 1: Creating new column
ALTER TABLE order_items
ADD COLUMN total_sales FLOAT;

-- Step 2: UPDATE the column to Store the value of quantity * price_per_unit
UPDATE order_items
SET total_sales = quantity * price_per_unit;

-- Step 3: In order to get the product name, order_id we need to join the orders_items
--first back to orders and then to product

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

	
---------------------------------------------
/*
2. REVENUE BY CATEGORY
Objective:
Calculate total revenue and profit generated by each product category
Challenge: 
Include the percentage contribution of each category total revenue and profit

In the Amazon e-commerce dataset, the order_items table includes product_id, price_per_unit 
and order_id and category _id is common to both products and category tables we need to join 
these three tables to solve the problem.

Steps:
1- Join order_items with products table on product_id
2- Join the result of the above with category table on category_id
*/
---------------------------------------------


SELECT
    c.category_id,
    c.category_name,
    ROUND(CAST(SUM(oi.total_sales) AS numeric), 2) AS category_total_sales,
    ROUND(
        CAST(
            (SUM(oi.total_sales) * 100.0 / (SELECT SUM(total_sales) FROM order_items))
            AS numeric
        ), 2
    ) AS sales_percentage,
    ROUND(CAST(SUM((p.price - p.cogs) * oi.quantity) AS numeric), 2) AS category_total_profit,
    ROUND(
        CAST(
            (SUM((p.price - p.cogs) * oi.quantity) * 100.0 /
             (SELECT SUM((p.price - p.cogs) * oi.quantity)
              FROM order_items oi
              JOIN products p ON p.product_id = oi.product_id))
            AS numeric
        ), 2
    ) AS profit_percentage
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
LEFT JOIN category c ON c.category_id = p.category_id
GROUP BY c.category_id, c.category_name
ORDER BY category_total_sales DESC;


---------------------------------------------
/*
3. AVERAGE ORDER VALUE
Objective:
Compute the average order value (aov) for each customer.
Challenge: 
Include customers with more than 5 orders

Steps:
1- Join orders and order_items to get total value per order.
2- Aggregate by customer to get total revenue and order count.
3- Filter customers with >5 orders.
4- Compute AOV = total revenue / number of orders.
*/
---------------------------------------------


SELECT
	c.customer_id,
	CONCAT(c.first_name, ' ', c.last_name) AS full_name,
	COUNT(o.order_id) AS total_orders,
	ROUND(CAST((SUM(oi.total_sales) / COUNT(o.order_id)) AS numeric), 2) AS aov
	
FROM
	orders AS o
JOIN 
	order_items AS oi
ON
	o.order_id = oi.order_id
JOIN 
	customers AS c
ON
	c.customer_id = o.customer_id
GROUP BY
	c.customer_id, full_name
HAVING	
	COUNT(o.order_id) > 5
ORDER BY
	aov DESC

	
---------------------------------------------
/*
4. MONTHLY SALES TREND
Objective:
Compute the monthly sales over the past 2 years.
Challenge: 
Display the sales trend, grouping match, return current_month_sale and last_month_sale

Steps:
1- Monthly sales (CTE): 
Extract year/month from order_date. --> Join orders + order_items --> Sum total_sales for each month (past 1 year).

2- Sales trend (CTE):
Current month = total_sales --> Previous month = LAG(total_sales) by year/month.
Calculate % change = (current - last) / last * 100
*/
---------------------------------------------


WITH monthly_sales AS (
    SELECT
        EXTRACT(YEAR FROM o.order_date) AS year,
        EXTRACT(MONTH FROM o.order_date) AS month,
        ROUND(SUM(oi.total_sales)::numeric, 2) AS total_sales
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '2 year'
    GROUP BY 1, 2
),
sales_trend AS (
    SELECT
        year,
        month,
        total_sales AS current_month_sale,
        LAG(total_sales) OVER (ORDER BY year, month) AS last_month_sale
    FROM monthly_sales
)
SELECT
    year,
    month,
    current_month_sale,
    last_month_sale,
    ROUND(
        ((COALESCE(current_month_sale, 0) - last_month_sale) * 100.0) / 
        NULLIF(last_month_sale, 0), 2
    ) AS percentage_trend
FROM sales_trend
ORDER BY year, month;


---------------------------------------------
/*
5. CUSTOMERS WITH NO PURCHASES
Objective:
Find customers with NO purchases
Challenge: 
List customer details and the time since their registration.

Steps:
1-Identify customers → Select from customers table.
2- Check purchases → Use LEFT JOIN with orders and filter where order_id IS NULL.
3- Return details → Show customer info.
*/
---------------------------------------------


-- Option 1:
SELECT 
	c.customer_id,
	CONCAT(c.first_name, ' ' , c.last_name) AS full_name,
	c.state_in AS state
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

--Option2:

SELECT
	customer_id,
	CONCAT(first_name, ' ' , last_name) AS full_name,
	state_in AS state
FROM
	customers
WHERE 
	customer_id NOT IN (
	SELECT 
		DISTINCT customer_id
	FROM 
		orders);


---------------------------------------------
/*
6. BEST SELLING CATEGORIES BY STATE

Objective:
Identify the top-selling product categories for each state
Challenge: Including total sales, order count, and percentage contribution to state-level sales.

Steps:
1. Join customers, orders, order_items, products, and category to access sales and location data.
2. Aggregate total sales and distinct order count by state and category.
3. Rank categories within each state using ROW_NUMBER() based on total sales.
4. Compute total sales per state to enable percentage contribution calculation.
5. Select top 2 categories per state with their sales, order count, and share of state-level sales.
*/
---------------------------------------------


WITH category_sales AS (
    SELECT 
        c.state_in AS state_in,
        cat.category_name AS category_name,
        COUNT(DISTINCT o.order_id) AS order_count,
        ROUND(CAST((SUM(oi.total_sales)) AS numeric), 2) AS total_sales
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    JOIN category cat ON p.category_id = cat.category_id
    GROUP BY c.state_in, cat.category_name
),
ranked_sales AS (
    SELECT 
        state_in,
        category_name,
        order_count,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY state_in ORDER BY total_sales DESC) AS rank
    FROM category_sales
),
state_totals AS (
    SELECT 
        state_in,
        SUM(total_sales) AS state_total_sales
    FROM category_sales
    GROUP BY state_in
)
SELECT 
    r.state_in, r.category_name,
    r.order_count, r.total_sales,
    ROUND(CAST(((r.total_sales * 100.0) / st.state_total_sales) AS numeric), 2) AS category_sales_percent,
    r.rank
FROM ranked_sales r
JOIN state_totals st ON r.state_in = st.state_in
WHERE r.rank <= 2
ORDER BY r.state_in, r.rank;


---------------------------------------------
/*
7. CUSTOMER LIFETIME VALUE (CLTV)

Objective:
Calculate the total value of orders placed by each customer over their lifetime.
Challenge: 
Rank xustomers based on their CLTV.

Steps:
1- Join customers, orders, and order_items to access customer purchases.
2- Aggregate SUM(oi.total_sales) per customer to compute CLTV.
3- Use DENSE_RANK() and PARTITIONS to rank customers by CLTV.
4- Return customer details + CLTV + rank.
*/
---------------------------------------------


SELECT 
	c.customer_id AS customer_id,
	CONCAT(c.first_name, ' ' , c.last_name) AS full_name,
	ROUND(CAST((SUM(oi.total_sales)) AS numeric),2) AS cltv,
	DENSE_RANK() OVER(ORDER BY SUM(oi.total_sales)DESC) AS cltv_rank

FROM customers AS c
JOIN
	orders AS o
ON
	c.customer_id = o.customer_id
JOIN
	order_items AS oi
ON
	oi.order_id = o.order_id
GROUP BY
	c.customer_id


---------------------------------------------
/*
8. INVENTORY STOCK ALERTS

Objective:
Identify products with low stock levels and provide alert categories.

Steps:
1. Join inventory with products and categories to get product details and classification.
2. Select stock, warehouse, and last restock date for each product.
3. Use CASE expressions to assign alert levels (OUT OF STOCK, CRITICAL, LOW, WARNING).
4. Filter only products with stock below 20 units.
5. Order results by severity of alert level and then by last restock date.
*/
---------------------------------------------


SELECT 
	p.product_id AS product_id, p.product_name AS product_name,
	cat.category_name AS category_name, i.inventory_id AS inventory_id,
	i.warehouse_id AS warehouse_id, i.stock AS avaliable_stock,
	i.last_stock_date AS last_stock_date,
	CASE 
	    WHEN i.stock = 0 THEN 'OUT OF STOCK'
	    WHEN i.stock BETWEEN 1 AND 4 THEN 'CRITICAL'
	    WHEN i.stock BETWEEN 5 AND 9 THEN 'LOW'
	    WHEN i.stock BETWEEN 10 AND 19 THEN 'WARNING'
	END AS alert_level
	
FROM 
	inventory AS i
JOIN
	products AS p
ON
	i.product_id = p.product_id
JOIN category AS cat
ON p.category_id = cat.category_id

WHERE i.stock < 20
ORDER BY 
    CASE 
        WHEN i.stock = 0 THEN 1
        WHEN i.stock < 5 THEN 2
        WHEN i.stock < 10 THEN 3
        ELSE 4
    END,
    i.last_stock_date ASC;


---------------------------------------------
/*
9. SHIPPING DELAYS

Objective:
Identify orders where the shipping date is more than 3 days after the order date.

Steps:
1. Join customers, orders, and shipping to access customer details, order info, and delivery provider.
2. Calculate the number of days between order_date and shipping_date.
3. Flag orders as DELAYED when days_to_ship > 4 (can be changed according to business logic).
4. Count total delayed orders per shipping provider.
5. Rank shipping providers based on the number of delays.
6. Return customer details, order info, delay flag, and provider ranking.
*/
---------------------------------------------


WITH delayed_orders AS (
    SELECT
        c.customer_id AS customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS customer_full_name,
        o.order_id AS order_id,
        c.state_in AS state_in,
        s.shipping_providers AS shipping_provider,
        (s.shipping_date - o.order_date) AS days_to_ship,
        CASE 
            WHEN (s.shipping_date - o.order_date) > 4 THEN 'DELAYED'
            ELSE 'ON TIME'
        END AS delay_status
    FROM customers AS c
    JOIN orders AS o ON c.customer_id = o.customer_id
    JOIN shipping AS s ON o.order_id = s.order_id
    WHERE (s.shipping_date - o.order_date) > 4
),
provider_delay_counts AS (
    SELECT 
        shipping_provider,
        COUNT(*) AS total_delays,
        RANK() OVER (ORDER BY COUNT(*) DESC) AS provider_rank
    FROM delayed_orders
    GROUP BY shipping_provider
)
SELECT 
    d.customer_id, d.customer_full_name,
    d.order_id, d.state_in, d.shipping_provider,
    d.days_to_ship, d.delay_status,
    p.total_delays, p.provider_rank
FROM delayed_orders AS d
JOIN provider_delay_counts AS p 
    ON d.shipping_provider = p.shipping_provider
ORDER BY p.provider_rank, d.days_to_ship DESC;


---------------------------------------------
/*
10. PAYMENT SUCCESS RATE

Objective:
Calculate the percentage of successful payments across all orders.

Steps:
1. Join payments with orders to scope payments to existing orders.
2. Count total payments and counts per payment_status.
3. Compute percentage per status = (status_count / total_payments) * 100.
4. Compute overall success rate = (successful_count / total_payments) * 100.
5. Return per-status breakdown (status, count, percentage) and overall success rate.
*/
--------------------------------------------


WITH status_counts AS (
    SELECT
        p.payment_status,
        COUNT(*) AS status_count
    FROM payments AS p
    JOIN orders AS o
      ON p.order_id = o.order_id
    GROUP BY p.payment_status
),
totals AS (
    SELECT
        SUM(status_count) AS total_payments
    FROM status_counts
)
SELECT
    sc.payment_status,
    sc.status_count,
    ROUND(
        (sc.status_count::numeric * 100.0) / NULLIF(t.total_payments::numeric, 0),
        2
    ) AS status_percentage
FROM status_counts sc
CROSS JOIN totals t
ORDER BY sc.status_count DESC;


--------------------------------------------
/*
11. TOP PERFORMING SELLERS

Objective:
Identify the top 5 sellers based on total sales value and evaluate their order success rates.

Steps:
1. Aggregate sales by seller (orders + order_items) to compute total sales value.
2. Select the top 5 sellers ranked by total sales.
3. For these sellers, count orders by status (Completed, Cancelled, etc.), excluding 'Inprogress' and 'Returned'.
4. Summarize completed vs cancelled orders and total orders per seller.
5. Calculate success rate as (completed_orders / total_orders) * 100.
6. Return seller details, order breakdown, and success percentage, ordered by success rate.
*/
--------------------------------------------


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

seller_report AS (
SELECT
	o.seller_id, ts.seller_name,
	ts.origin, o.order_status,
	COUNT(*) AS total_orders
	
FROM top_sellers AS ts
JOIN orders AS o
ON ts.seller_id = o.seller_id
WHERE o.order_status NOT IN ('Inprogress', 'Returned')
GROUP BY 1,2,3,4
)

SELECT
	seller_id, seller_name, origin,
	SUM(CASE WHEN order_status = 'Completed' 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) AS total_orders,
	ROUND((SUM(CASE WHEN order_status = 'Completed' THEN total_orders ELSE 0 END) :: numeric /
	SUM(total_orders):: numeric * 100),2) AS success_orders_percentage
FROM 
	seller_report
GROUP BY 1,2,3
ORDER BY success_orders_percentage DESC


--------------------------------------------
/*
12. CALCULATE PROFIT MARGIN

Objective:
Calculate the profit margin for each product (difference between price and cost of goods).
Challenge:
Rank products by profit margin, showing highest to lowest.
Formula: profit_margin = (price - cogs) * quantity

Steps:
1. Join products with order_items and orders to access product details, quantities, and order status.
2. Filter only 'Completed' orders to ensure valid sales.
3. Aggregate total quantity sold per product using SUM(oi.quantity).
4. Calculate profit margin as SUM((price - cogs) * quantity).
5. Group results by product_id and product_name.
6. Order products by profit_margin in descending order to rank top performers.
*/
--------------------------------------------


SELECT
    p.product_id,
    p.product_name,
    p.cogs AS cost_of_goods,
    p.price AS product_unit_price,
    SUM(oi.quantity) AS num_products_sold,
    ROUND(SUM((p.price - p.cogs) * oi.quantity)::numeric, 2) AS profit_margin
FROM products AS p
JOIN order_items AS oi
    ON p.product_id = oi.product_id
JOIN orders AS o
    ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.product_id, p.product_name, p.cogs, p.price
ORDER BY profit_margin DESC;


--------------------------------------------
/*
13. MOST RETURNED PRODUCTS

Objective:
Find the top 10 products with the highest number of returns.

Challenge:
Calculate the return rate as a percentage of total units sold for each product.

Steps:
1. Join products with order_items and orders to access product details, quantities, and order status.
2. Aggregate total units sold per product using SUM(oi.quantity).
3. Count total returned units per product using SUM(CASE WHEN order_status = 'Returned' THEN oi.quantity ELSE 0 END).
4. Compute return percentage = (total_returned / total_units_sold) * 100.
5. Group results by product_id and product_name.
6. Order products by return_percentage in descending order.
7. Limit results to the top 10 most returned products.
*/
--------------------------------------------


SELECT 
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS total_units_sold,
    SUM(CASE WHEN o.order_status = 'Returned' THEN oi.quantity ELSE 0 END) AS total_returned,
    ROUND(
        (SUM(CASE WHEN o.order_status = 'Returned' THEN oi.quantity ELSE 0 END)::numeric 
        / NULLIF(SUM(oi.quantity),0)::numeric) * 100, 2
    ) AS return_percentage
FROM products AS p
JOIN order_items AS oi
    ON p.product_id = oi.product_id
JOIN orders AS o
    ON o.order_id = oi.order_id
GROUP BY p.product_id, p.product_name
ORDER BY return_percentage DESC
LIMIT 10;


--------------------------------------------
/*
14. INACTIVE SELLERS

Objective:
Identify sellers who have not made any sales in the last 12 months.

Challenge:
Show the last sale date and total sales from those inactive sellers.

Steps:
1. Create a CTE (six_month_non_sellers) to select sellers whose IDs do not appear in orders placed within the last 10 months.
2. Join this inactive seller list with orders to retrieve their historical sales activity.
3. Join order_items to access sales values for those orders.
4. Use MAX(order_date) to capture the last sale date for each inactive seller.
5. Use MAX(oi.total_sales) to show the most recent sale amount (or adjust to SUM if total lifetime sales are desired).
6. Group results by seller_id and seller_name.
7. Order results by last_sale_date to display inactivity chronologically.
*/
--------------------------------------------


WITH six_month_non_sellers AS 
(
SELECT * FROM sellers
WHERE seller_id NOT IN (SELECT seller_id 
						FROM orders 
						WHERE 
						order_date >= CURRENT_DATE - INTERVAL '12 month'
						)
)

SELECT
	sm.seller_id,
	sm.seller_name,
	MAX(o.order_date) AS last_sale_date,
	SUM(oi.total_sales::numeric) AS last_sale_amount
FROM orders AS o
JOIN six_month_non_sellers AS sm
ON sm.seller_id = o.seller_id
JOIN order_items AS oi
ON oi.order_id = o.order_id
GROUP BY 1,2
ORDER BY last_sale_date


--------------------------------------------
/*
15. CLASSIFY CUSTOMERS INTO RETURNING OR NEW

Objective:
Classify customers based on the number of returned orders (Show top ten customers). 
If a customer has more than 5 returns → Returning, otherwise → New.

Challenge:
List customer_id, name, total_orders, and total_returns and rank based on total_returns.

Steps:
1. Join customers with orders to access customer details and order info.
2. Join order_items if needed for completeness (though not required for counting returns).
3. Count total orders per customer using COUNT(o.order_id).
4. Count total returns per customer using SUM(CASE WHEN order_status = 'Returned' THEN 1 ELSE 0 END).
5. Build a CTE (customer_report) to store aggregated results.
6. Classify customers with CASE: >5 returns = 'Returning', else 'New'.
7. Return customer_id, full_name, total_orders, total_returns, and classification.
*/
--------------------------------------------


WITH customer_report AS (
    SELECT 
        c.customer_id AS customer_id,
        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 customers AS c
    JOIN orders AS o
        ON c.customer_id = o.customer_id
    GROUP BY 1,2
)
SELECT 
    customer_id,
    full_name,
    total_orders,
    total_returns,
    RANK() OVER (ORDER BY total_returns DESC) AS return_rank,
    CASE 
        WHEN total_returns > 5 THEN 'Returning_customer' 
        ELSE 'New' 
    END AS customer_status
FROM customer_report
ORDER BY return_rank;


--------------------------------------------
/*
16. IDENTIFY TOP 5 CUSTOMERS BY ORDERS IN EACH STATE

Objective:
Identify the top 5 customers in each state with the highest number of orders 
(completed or in progress).

Challenge:
List customer_id, name, total_orders, and total_sales, and rank customers 
within each state based on total_sales.

Steps:
1. Join customers with orders to access customer details and order info.
2. Join order_items to calculate sales values per order.
3. Filter out 'Returned' orders to keep only valid sales (Completed or In Progress).
4. Aggregate total orders per customer using COUNT(o.order_id).
5. Aggregate total sales per customer using SUM(oi.total_sales).
6. Build a CTE (customer_report) with customer_id, name, state, total_orders, and total_sales.
7. Use RANK() with PARTITION BY state_in to rank customers within each state by total_sales.
8. Select the top 5 customers per state.
*/
--------------------------------------------


WITH customer_report AS (
    SELECT 
        c.customer_id,
        CONCAT(c.first_name, ' ', c.last_name) AS full_name,
        c.state_in,
        COUNT(o.order_id) AS total_orders,
        SUM(oi.total_sales::numeric) AS total_succ_sales
    FROM customers AS c
    JOIN orders AS o
        ON c.customer_id = o.customer_id
    JOIN order_items AS oi
        ON o.order_id = oi.order_id
    WHERE o.order_status IN ('Completed', 'Inprogress')
    GROUP BY c.customer_id, full_name, c.state_in
)
SELECT 
    customer_id, full_name,
    state_in, total_orders,
    total_succ_sales, sales_rank
FROM (
    SELECT 
        customer_id, full_name,
        state_in, total_orders,
        total_succ_sales,
        RANK() OVER (PARTITION BY state_in ORDER BY total_succ_sales DESC) AS sales_rank
    FROM customer_report
) ranked
WHERE sales_rank <= 5
ORDER BY state_in, sales_rank;


--------------------------------------------
/*
17. REVENUE BY SHIPPING PROVIDER

Objective:
Calculate the total revenue handled by each shipping provider.

Challenge:
List shipping provider, the total number of orders handled, and the average delivery time for each provider.

Steps:
1. Join shipping with orders to access provider details and order dates.
2. Join order_items to calculate sales values per order.
3. Filter out 'Returned' orders to keep only valid sales (Completed or In Progress).
4. Aggregate total orders handled per provider using COUNT(o.order_id).
5. Aggregate total revenue per provider using SUM(oi.total_sales).
6. Calculate average delivery time as AVG(shipping_date - order_date).
7. Group results by shipping provider.
8. Return shipping provider, total_orders, total_sales, and avg_delivery_days.
*/
--------------------------------------------


SELECT
	sp.shipping_providers AS shipping_provider,
	COUNT(o.order_id) AS total_orders,
	ROUND((AVG(sp.shipping_date - o.order_date)::numeric),2) AS avg_delivery_days,
	SUM(oi.total_sales::numeric) AS total_sales_by_shipping

FROM shipping AS sp
JOIN order_items AS oi
ON sp.order_id = oi.order_id
JOIN orders AS o
ON o.order_id = oi.order_id
WHERE o.order_status IN ('Completed','Inprogress')
GROUP BY sp.shipping_providers
ORDER BY total_sales_by_shipping DESC;


--------------------------------------------
/*
18. TOP TEN PRODUCTS WITH THE HIGHEST DECREASING REVENUE RATIO

Objective:
Calculate the products with the largest percentage revenue decrease from 2022 to 2023.

Challenge:
List product_id, product_name, category_name, previous-year revenue (2022), current-year revenue (2023),
and the decrease ratio percentage.

Formula:
Decrease ratio (%) = ((revenue_2022 - revenue_2023) / revenue_2022) * 100

Steps:
1. Build a CTE (previous_yr) that aggregates 2022 revenue per product and category, excluding cancelled and returned orders.
2. Build a CTE (current_yr) that aggregates 2023 revenue per product and category, excluding cancelled and returned orders.
3. Join previous_yr to current_yr by product_id to compare year-over-year revenue (inner join keeps products present in both years).
4. Compute the decrease ratio using NULLIF(revenue_2022, 0) to avoid division by zero.
5. Rank products by decrease ratio in descending order (largest decrease first).
6. Return product_id, product_name, category_name, revenue_2022, revenue_2023, decrease ratio, and rank.
7. Limit the result to the top 10 products.
*/
--------------------------------------------


WITH previous_yr AS (
    SELECT
        p.product_id,
        p.product_name,
        cat.category_name AS category_name,
        SUM(oi.total_sales)::numeric AS total_2022_revenue
    FROM products AS p
    JOIN order_items AS oi
        ON p.product_id = oi.product_id
    JOIN orders AS o
        ON o.order_id = oi.order_id
    JOIN category AS cat
        ON cat.category_id = p.category_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2022
      AND o.order_status NOT IN ('Cancelled', 'Returned')
    GROUP BY p.product_id, p.product_name, cat.category_name
),
current_yr AS (
    SELECT
        p.product_id,
        p.product_name,
        cat.category_name AS category_name,
        SUM(oi.total_sales)::numeric AS total_2023_revenue
    FROM products AS p
    JOIN order_items AS oi
        ON p.product_id = oi.product_id
    JOIN orders AS o
        ON o.order_id = oi.order_id
    JOIN category AS cat
        ON cat.category_id = p.category_id
    WHERE EXTRACT(YEAR FROM o.order_date) = 2023
      AND o.order_status NOT IN ('Cancelled', 'Returned')
    GROUP BY p.product_id, p.product_name, cat.category_name
)
SELECT
    p.product_id,
    p.product_name,
    p.category_name,
    ROUND(p.total_2022_revenue, 2) AS total_2022_revenue,
    ROUND(c.total_2023_revenue, 2) AS total_2023_revenue,
    ROUND(
        ((p.total_2022_revenue - c.total_2023_revenue)
         / NULLIF(p.total_2022_revenue, 0)) * 100
    , 2) AS decrease_ratio_percent,
    RANK() OVER (
        ORDER BY (p.total_2022_revenue - c.total_2023_revenue)
                 / NULLIF(p.total_2022_revenue, 0) DESC
    ) AS decrease_rank
FROM previous_yr AS p
JOIN current_yr AS c
    ON p.product_id = c.product_id
ORDER BY decrease_rank
LIMIT 10;


--------------------------------------------
/*
18. STORE PROCEDURE

Objective:
Create a procedure that updates the inventory table as soon as a product is sold.

Challenge:
After inserting sales records into the orders and order_items tables, the procedure should 
decrease the stock in the inventory table based on the purchased quantity.

Steps:
1. Accept input parameters for order details (order_id, customer_id, seller_id, order_item_id, product_id, quantity).
2. Check product availability in the inventory table to ensure sufficient stock exists.
3. Retrieve the product’s price and name from the products table.
4. If stock is sufficient:
   a. Insert a new record into the orders table with the order details.
   b. Insert a new record into the order_items table with product, quantity, price, and total sales.
   c. Update the inventory table by subtracting the purchased quantity from current stock.
   d. Raise a notice confirming the sale and inventory update.
5. If stock is insufficient:
   a. Raise a notice indicating the product is unavailable or stock is insufficient.
*/
--------------------------------------------

CREATE OR REPLACE PROCEDURE add_sales(
    p_order_id INT,
    p_customer_id INT,
    p_seller_id INT,
    p_order_item_id INT,
    p_product_id INT,
    p_quantity INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INT;
    v_price FLOAT;
    v_product_name VARCHAR(50);
BEGIN
    -- Check product availability in inventory
    SELECT COUNT(*) INTO v_count
    FROM inventory
    WHERE product_id = p_product_id AND stock >= p_quantity;

    -- Fetch product name and price
    SELECT price, product_name INTO v_price, v_product_name
    FROM products
    WHERE product_id = p_product_id;

    IF v_count > 0 THEN
        -- Add sales record to orders table
        INSERT INTO orders (order_id, order_date, customer_id, seller_id)
        VALUES (p_order_id, CURRENT_DATE, p_customer_id, p_seller_id);

        -- Add sales record to order_items table
        INSERT INTO order_items (order_item_id, order_id, product_id, quantity, price_per_unit, total_sales)
        VALUES (p_order_item_id, p_order_id, p_product_id, p_quantity, v_price, p_quantity * v_price);

        -- Update inventory stock
        UPDATE inventory
        SET stock = stock - p_quantity
        WHERE product_id = p_product_id;

        RAISE NOTICE 'Thank you! Product % sales has been added and inventory stock updated.', v_product_name;
    ELSE
        RAISE NOTICE 'Thank you for your info. Product % is not available or insufficient stock.', v_product_name;
    END IF;
END;
$$


CALL add_sales(
    89942168,   -- p_order_id (example: 899468)
    898,       -- p_customer_id (example: 898)
    3,       -- p_seller_id (example: Tech Armor)
    1094521678,   -- p_order_item_id (example: 10945678)
    62,       -- p_product_id (example: 62 for 'Samsung 34-Inch Curved Monitor')
    13       -- p_quantity (example: 30 units)
);

SELECT o.order_id, oi.order_item_id, p.product_id,p.product_name, oi.quantity, s.seller_id, o.order_date
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
JOIN sellers s
ON s.seller_id = o.seller_id
JOIN inventory as i
ON i.product_id = p.product_id
WHERE o.order_id = 89942168;