05_operations.sql

/* ============================================================
   Fulfilment and payment operations (Q9, Q10, Q14, Q15, Q18)

   Operational questions spanning shipping and payments. Shipping
   holds 21,141 rows against 21,629 orders, because the 488
   cancelled orders were never shipped. That is correct rather
   than missing data.

   There is no delivery date anywhere in this schema, so delivery
   time cannot be derived and is not attempted. Three of these
   questions return zero rows or prove unanswerable. Each says so
   and shows the evidence, because a null result with proof is a
   finding rather than a gap.
   ============================================================ */


-- ============================================================
-- Q9 | Orders despatched more than seven days late
-- Business question: which orders took more than seven days to
--   dispatch after being placed?
-- Approach: subtract order date from shipping date, then report
--   the full lag distribution as supporting evidence.
-- Note: none exist. The longest dispatch lag in the data is five
--   days, and the spread across one to five days is near uniform.
-- ============================================================

-- Part A: the question as asked
SELECT
    o.order_id,
    o.order_date,
    s.shipping_date,
    s.shipping_provider,
    s.shipping_date - o.order_date AS dispatch_lag_days
FROM orders AS o
INNER JOIN shipping AS s
    ON s.order_id = o.order_id
WHERE s.shipping_date - o.order_date > 7
ORDER BY dispatch_lag_days DESC;

-- Part B: the evidence behind the zero
SELECT
    s.shipping_date - o.order_date AS dispatch_lag_days,
    -- COUNT counts the rows that landed in this one bucket
    COUNT(*) AS orders,
    -- SUM adds every bucket's count down into one total, then writes
    -- that total onto each row so it can show its share
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_shipped
FROM orders AS o
INNER JOIN shipping AS s
    ON s.order_id = o.order_id
GROUP BY 1
ORDER BY 1;


-- ============================================================
-- Q10 | Payment success rate
-- Business question: what share of payments succeeded, failed,
--   or were refunded?
-- Approach: count payments by status and express each as a share
--   of all transactions.
-- Note: refunds are reversals, not successes. The success rate
--   is 84.61%, not the 97.74% that adding refunds back gives.
-- ============================================================

SELECT
    payment_status,
    COUNT(*) AS transactions,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_payments
FROM payments
GROUP BY 1
ORDER BY transactions DESC;


-- ============================================================
-- Q14 | Paid orders awaiting shipment
-- Business question: which successfully paid orders have no
--   shipping record?
-- Approach: left join shipping to orders and keep rows where no
--   shipping record exists.
-- Note 1: none exist. All 488 orders without a shipping record
--   are cancelled with a failed payment.
-- Note 2: the left join is essential. With an inner join the null
--   check could never be true, so the zero would be guaranteed by
--   the query rather than found in the data.
-- ============================================================

SELECT
    o.order_id,
    o.order_date,
    o.order_status,
    DATE '2024-07-30' - o.order_date AS days_awaiting_shipment
FROM orders AS o
INNER JOIN payments AS p
    ON p.order_id = o.order_id
LEFT JOIN shipping AS s
    ON s.order_id = o.order_id
WHERE p.payment_status = 'Payment Successed'
  AND s.order_id IS NULL
ORDER BY o.order_date;


-- ============================================================
-- Q15 | Cross-sell opportunities
-- Business question: which products are frequently bought
--   together in the same order?
-- Approach: count orders holding more than one distinct product,
--   and measure the largest basket in the data.
-- Note: not answerable here. No order contains more than one
--   product, so no pair co-occurs and affinity is undefined.
-- ============================================================

WITH baskets AS (
    SELECT
        order_id,
        COUNT(*) AS line_items,
        COUNT(DISTINCT product_id) AS distinct_products
    FROM order_items
    GROUP BY order_id
)
SELECT
    COUNT(*) AS orders_with_line_items,
    MAX(line_items) AS max_line_items,
    COUNT(*) FILTER (WHERE distinct_products > 1) AS orders_with_multiple_products
FROM baskets;

-- SELECT
--     oi_a.product_id   AS product_a_id,
--     pa.product_name   AS product_a,
--     oi_b.product_id   AS product_b_id,
--     pb.product_name   AS product_b,
--     COUNT(DISTINCT oi_a.order_id) AS orders_containing_both
-- FROM order_items AS oi_a
-- INNER JOIN order_items AS oi_b
--     ON oi_b.order_id   = oi_a.order_id
--    AND oi_b.product_id > oi_a.product_id
-- INNER JOIN products AS pa
--     ON pa.product_id = oi_a.product_id
-- INNER JOIN products AS pb
--     ON pb.product_id = oi_b.product_id
-- GROUP BY 1, 2, 3, 4
-- ORDER BY orders_containing_both DESC
-- LIMIT 10;

-- ============================================================
-- Q18 | Shipping provider performance
-- Business question: how much order value does each shipping
--   provider handle, and how quickly do they dispatch?
-- Approach: group shipped orders by provider, reporting value,
--   average dispatch lag and returns handled.
-- Note: delivery time is not derivable, as the schema holds no
--   delivery date. Dispatch lag is the closest available measure.
-- ============================================================

SELECT
    s.shipping_provider,
    COUNT(DISTINCT o.order_id) AS orders_handled,
    ROUND(SUM(oi.total_sale), 2) AS shipped_order_value,
    ROUND(AVG(s.shipping_date - o.order_date), 2) AS avg_dispatch_lag_days,
    COUNT(DISTINCT o.order_id) FILTER (WHERE s.return_date IS NOT NULL)
        AS returned_orders
FROM orders AS o
INNER JOIN order_items AS oi
    ON oi.order_id = o.order_id
INNER JOIN shipping AS s
    ON s.order_id = o.order_id
GROUP BY 1
ORDER BY shipped_order_value DESC;

-- If we wanted to add a return_rate_pct, we would simply add the above query in a cte, and write this query

-- SELECT
--     shipping_provider,
--     orders_handled,
--     shipped_order_value,
--     avg_dispatch_lag_days,
--     returned_orders,
--     ROUND(returned_orders * 100.0 / orders_handled, 2) AS return_rate_pct
-- FROM provider_totals (cte from above query)
-- ORDER BY shipped_order_value DESC;