03_product_and_inventory.sql

/* ============================================================
   Product and inventory analysis (Q8, Q12, Q13)

   Product-level questions across 765 products, each with exactly
   one inventory row. Q8 reads inventory directly. Q12 and Q13
   join order_items to orders so they can filter on order status.

   Status handling differs by question and each one states which
   it uses. Q12 counts completed orders only, since profit on a
   returned item is not profit. Q13 measures returns against
   resolved units, meaning completed plus returned, because
   cancelled and in-progress orders have no return outcome yet.
   ============================================================ */


-- ============================================================
-- Q8 | Products low on stock
-- Business question: which products have fewer than ten units
--   left in the warehouse?
-- Approach: read inventory, join products for the name, and keep
--   rows below the threshold.
-- ============================================================

SELECT
    p.product_id,
    p.product_name,
    i.warehouse_id,
    i.stock AS current_stock,
    i.last_stock_date AS last_restock_date,
    DATE '2024-07-30' - i.last_stock_date AS days_since_last_restock
FROM inventory AS i
INNER JOIN products AS p
    ON p.product_id = i.product_id
WHERE i.stock < 10
ORDER BY i.stock, p.product_name;


-- ============================================================
-- Q12 | Product profit and margin
-- Business question: which products earn the most profit, and
--   which carry the highest margin?
-- Approach: total completed revenue and cost per product, then
--   rank separately on margin and on absolute profit.
-- Note: price_per_unit never differs from the product's list
--   price, so margin percent is fixed per product and only the
--   profit ranking reflects actual trading.
-- ============================================================

WITH product_profit AS (
    SELECT
        p.product_id,
        p.product_name,
        ROUND(SUM(oi.total_sale), 2) AS completed_revenue,
        ROUND(SUM(oi.total_sale - p.cogs * oi.quantity), 2) AS gross_profit,
        ROUND(SUM(oi.total_sale - p.cogs * oi.quantity) * 100.0
              / SUM(oi.total_sale), 2) AS margin_pct
    FROM order_items AS oi
    INNER JOIN products AS p
        ON p.product_id = oi.product_id
    INNER JOIN orders AS o
        ON o.order_id = oi.order_id
    WHERE o.order_status = 'Completed'
    GROUP BY 1, 2
)

SELECT
    product_id,
    product_name,
    completed_revenue,
    gross_profit,
    margin_pct,
    DENSE_RANK() OVER (
        ORDER BY margin_pct DESC
    ) AS margin_rank,
    DENSE_RANK() OVER (
        ORDER BY gross_profit DESC
    ) AS profit_rank
FROM product_profit
ORDER BY margin_rank, profit_rank;


-- ============================================================
-- Q13 | Product return rates
-- Business question: which products are returned most often as
--   a share of units sold?
-- Approach: count returned units against resolved units per
--   product, with a floor of twenty units to exclude noise.
-- Note 1: returns are measured in units, not orders, as asked. 
-- Note 2: products below twenty resolved units are excluded. Without
--   that floor the top of the list is products that sold once.
-- ============================================================

WITH product_returns AS (
    SELECT
        p.product_id,
        p.product_name,
        SUM(oi.quantity) AS units_ordered,
        SUM(oi.quantity) FILTER (WHERE o.order_status IN ('Completed', 'Returned')) AS units_resolved,
        COALESCE(SUM(oi.quantity) FILTER (WHERE o.order_status = 'Returned'), 0) AS units_returned
    FROM order_items AS oi
    INNER JOIN products AS p
        ON p.product_id = oi.product_id
    INNER JOIN orders AS o
        ON o.order_id = oi.order_id
    GROUP BY 1, 2
)

SELECT
    product_id,
    product_name,
    units_ordered,
    units_resolved,
    units_returned,
    ROUND(units_returned * 100.0 / units_resolved, 2) AS unit_return_rate_pct
FROM product_returns
WHERE units_resolved >= 20   -- volume floor, one return on a tiny sample is not a rate
ORDER BY unit_return_rate_pct DESC, units_resolved DESC
LIMIT 10;