WITH total_sales_by_customer AS (
    SELECT 
        c.customer_id, 
        c.customer_name,
        SUM(o.order_total) AS total_spent
    FROM customers c
    LEFT JOIN orders o 
        ON c.customer_id = o.customer_id
    WHERE o.order_date >= '2023-01-01'
    GROUP BY c.customer_id, c.customer_name
),
customer_details AS (
    SELECT
        tsbc.customer_id,
        tsbc.customer_name,
        tsbc.total_spent,
        -- Subquery to count total orders placed by each customer
        (SELECT COUNT(*) 
         FROM orders o2 
         WHERE o2.customer_id = tsbc.customer_id) AS total_orders
    FROM total_sales_by_customer tsbc
)
SELECT 
    cd.customer_id,
    cd.customer_name,
    cd.total_spent,
    cd.total_orders,
    -- Example of a window function to rank customers by total spent
    ROW_NUMBER() OVER (ORDER BY cd.total_spent DESC) AS spend_rank,
    -- Another subquery to show the latest order date for each customer
    (
        SELECT MAX(o3.order_date)
        FROM orders o3
        WHERE o3.customer_id = cd.customer_id
    ) AS last_order_date,
    -- Example of potential profitability check (assuming cost factor is in another table)
    (
        SELECT AVG(p.cost_of_goods_sold)
        FROM products p
        JOIN order_items oi ON p.product_id = oi.product_id
        JOIN orders o4 ON oi.order_id = o4.order_id
        WHERE o4.customer_id = cd.customer_id
    ) AS avg_cogs_for_customer
FROM customer_details cd
ORDER BY cd.total_spent DESC;