Ask Your Data Anything,
We'll Squeel It into SQL

Here's the SQL query:

WITH RegionalCategoryRevenue AS (
    -- Calculate total revenue for each category within each region
    SELECT
        c.region,
        pc.category_name,
        SUM(oi.price * oi.quantity) AS total_category_revenue
    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 product_categories pc ON p.category_id = pc.category_id
    GROUP BY
        c.region,
        pc.category_name
),
RankedRegionalCategoryRevenue AS (
    -- Rank categories by revenue within each region
    SELECT
        region,
        category_name,
        total_category_revenue,
        RANK() OVER (PARTITION BY region ORDER BY total_category_revenue DESC) as rank_in_region
    FROM
        RegionalCategoryRevenue
)
-- Select the top ranked category (rank = 1) for each region
SELECT
    region,
    category_name,
    total_category_revenue
FROM
    RankedRegionalCategoryRevenue
WHERE
    rank_in_region = 1
ORDER BY
    region;