02_customer_analysis.sql

/* ============================================================
   Customer analysis (Q5, Q7, Q16, Q17)

   All four questions work at customer level, joining orders to
   customers. Of 898 customers, 686 have ordered, 212 never have,
   and 520 have at least one completed order.

   Group on customer_id, never on name — 535 customers share a
   full name with someone else, so grouping by name quietly
   merges different people.
============================================================ */

-- ============================================================
--  Q5 | Customers with no purchases
--  Business question: which registered customers have never
--    placed an order?
--  Approach: return every customer with no matching row in orders.
--  Note: the original challenge also asked for time since
--    registration. No registration date exists in this schema,
--    so that half is not derivable and is excluded rather than
--    approximated.
-- ============================================================

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state
FROM customers AS c
WHERE NOT EXISTS (                         -- keep this customer only if the inner query finds nothing
    SELECT 1                               -- the 1 is never read it's like a placeholder; EXISTS only asks "did any row come back? and the query returns True or false in a sense"
    FROM orders AS o
    WHERE o.customer_id = c.customer_id    -- is the current customer_id in the orders table
)
ORDER BY c.customer_id;


-- ============================================================
-- Q7 | Customer lifetime value
-- Business question: how much completed revenue has each
--   customer generated over their lifetime, and who ranks
--   highest?
-- Approach: total each customer's completed spend and rank it.
-- Note: completed orders only. Cancelled and returned spend was
--   never realised, so it is not lifetime value.
-- ============================================================

SELECT
    c.customer_id,
    c.first_name||' '||c.last_name AS customer_name,
    ROUND(SUM(oi.total_sale), 2) AS completed_lifetime_value,
    COUNT(DISTINCT o.order_id) AS completed_orders,
    DENSE_RANK() OVER (
        ORDER BY SUM(oi.total_sale) DESC
    ) AS clv_rank
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
    ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY 1, 2
ORDER BY clv_rank, customer_id;


-- ============================================================
-- Q16 | Customer segmentation by return behaviour
-- Business question: which customers have returned more than
--   five orders, and how large is that group?
-- Approach: count each customer's orders and returns, then label
--   anyone above five returns as high-return.
-- Note: some customers show returns equal to orders, meaning
--   every order they placed came back, which is implausible.
-- ============================================================

WITH customer_activity AS (
    SELECT 
        c.customer_id, 
        c.first_name||' '||c.last_name AS customer_name,
        COUNT(DISTINCT o.order_id) AS total_orders,
        COUNT(*) FILTER (WHERE o.order_status = 'Returned') AS total_returns
    FROM orders AS o 
    INNER JOIN customers AS c 
        ON c.customer_id = o.customer_id 
    GROUP BY 1, 2
)

SELECT 
    customer_id, 
    customer_name, 
    total_orders, 
    total_returns,
    ROUND((total_returns * 100.0 / total_orders), 2) AS return_rate_pct,
    CASE WHEN total_returns > 5 THEN 'High-return' ELSE 'Standard' END AS customer_segment
FROM customer_activity 
ORDER BY total_returns DESC, total_orders DESC;


-- ============================================================
-- Q17 | Top five customers by state
-- Business question: which five customers place the most orders
--   in each state?
-- Approach: rank customers by order count within each state and
--   keep the top five.
-- Note: ties on order count are broken by spend. 190 rows, not
--   195, as five states have fewer than five customers.
-- ============================================================

WITH ranked AS (
    SELECT
        c.state,
        c.customer_id,
        c.first_name||' '||c.last_name AS customer_name,
        COUNT(DISTINCT o.order_id)     AS total_orders,
        SUM(oi.total_sale)             AS gross_order_value,
        DENSE_RANK() OVER (
            PARTITION BY c.state
            ORDER BY COUNT(DISTINCT o.order_id) DESC,
                     SUM(oi.total_sale) DESC
        ) AS customer_rank
    FROM orders AS o
    INNER JOIN customers AS c
        ON c.customer_id = o.customer_id
    INNER JOIN order_items AS oi
        ON oi.order_id = o.order_id
    GROUP BY 1, 2, 3
)

SELECT 
    state, 
    customer_id, 
    customer_name, 
    total_orders,
    ROUND(gross_order_value, 2) AS gross_order_value, 
    customer_rank
FROM ranked
WHERE customer_rank <= 5    -- Filtering to keep the top 5 customers only 
ORDER BY state, customer_rank;