04_seller_performance.sql

/* ============================================================
   Seller performance analysis (Q11, Q21)

   Seller-level questions across 54 sellers. Both join orders to
   sellers, and Q21 uses a left join so that sellers with no
   orders at all still appear.

   Q11 counts completed orders only, since revenue on a cancelled
   or returned order was never earned. Dates are fixed to the end
   of the data on 30 July 2024.
   ============================================================ */

-- ============================================================
-- Q11 | Top sellers by completed revenue
-- Business question: which five sellers generate the most
--   completed revenue, and how reliably do their orders close?
-- Approach: rank sellers by completed revenue, then count each
--   one's completed and cancelled orders alongside.
-- Note: the completion rate is measured against decided orders
--   only, meaning completed plus cancelled. Orders still in
--   progress have no outcome yet.
-- ============================================================

WITH seller_performance AS (
    SELECT
        s.seller_id,
        s.seller_name,
        SUM(oi.total_sale) FILTER (WHERE o.order_status = 'Completed') AS completed_revenue,
        COUNT(DISTINCT o.order_id) FILTER (WHERE o.order_status = 'Completed') AS completed_orders,
        COUNT(DISTINCT o.order_id) FILTER (WHERE o.order_status = 'Cancelled') AS cancelled_orders,
        COUNT(DISTINCT o.order_id) FILTER (WHERE o.order_status IN ('Completed', 'Cancelled')) AS decided_orders
    FROM orders AS o
    INNER JOIN sellers AS s
        ON s.seller_id = o.seller_id
    INNER JOIN order_items AS oi
        ON oi.order_id = o.order_id
    GROUP BY 1, 2
)

SELECT
    seller_id,
    seller_name,
    ROUND(completed_revenue, 2) AS completed_revenue,
    completed_orders,
    cancelled_orders,
    decided_orders,
    ROUND(completed_orders * 100.0 / decided_orders, 2) AS pct_decided_completed
FROM seller_performance
ORDER BY completed_revenue DESC
LIMIT 5;


-- ============================================================
-- Q21 | Sellers with no recent sales
-- Business question: which sellers have made no sale in the six
--   months to 30 July 2024?
-- Approach: keep sellers with no order in that window, using a
--   left join so those with no orders at all still appear.
-- Note: both sellers returned have never sold anything, so their
--   last sale date is null. Onboarded but never trading is a
--   different problem from churn.
-- ============================================================

SELECT
    s.seller_id,
    s.seller_name,
    MAX(o.order_date) AS last_sale_date,
    COALESCE(ROUND(SUM(oi.total_sale), 2), 0) AS gross_lifetime_value,
    COUNT(DISTINCT o.order_id) AS lifetime_orders
FROM sellers AS s
LEFT JOIN orders AS o
    ON o.seller_id = s.seller_id
LEFT JOIN order_items AS oi
    ON oi.order_id = o.order_id
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS r
    WHERE r.seller_id = s.seller_id -- match to the seller in the outer query 
      AND r.order_date >= DATE '2024-07-30' - INTERVAL '6 months' 
)
GROUP BY 1, 2
ORDER BY s.seller_id;