SET @return_fee = 10;
SET @order_handling_fee = 25;
SELECT
    sku,
    COUNT(order_id) AS total_orders,
    ROUND(AVG(profit), 2) AS avg_profit,
    ROUND(price, 2) AS price,
    ROUND(cost, 2) AS cost,
    SUM(qty_invoiced) AS total_qty_invoiced,
    SUM(qty_refunded) AS total_qty_refunded,
    GROUP_CONCAT(ROUND(t.profit, 2)) AS profits_per_order,
    GROUP_CONCAT(ROUND(order_return_rate)) AS orders_returnrates,
    ROUND(AVG(order_return_rate)) AS avg_order_returnrate,
    CONCAT(ROUND((SUM(qty_refunded) / SUM(qty_invoiced)) * 100),
            '%') AS product_returnrate,
    GROUP_CONCAT(DISTINCT increment_id) AS orders,
    GROUP_CONCAT(DISTINCT bought_together) AS bought_together
FROM
    (SELECT
        main_table.order_id AS order_id,
            orders.increment_id AS increment_id,
            main_table.product_id AS product_id,
            IF(main_table.parent_item_id IS NULL, main_table.sku, parent_item.sku) AS sku,
            main_table.product_type AS product_type,
            main_table.created_at AS created_at,
            main_table.qty_invoiced,
            main_table.qty_refunded,
            order_totals.order_return_rate,
            order_totals.bought_together AS bought_together,
            IF(main_table.parent_item_id IS NULL, main_table.base_cost, parent_item.base_cost) AS cost,
            IF(main_table.parent_item_id IS NULL, main_table.price, parent_item.price) AS price,
            IF(main_table.parent_item_id IS NULL, main_table.price, parent_item.price) * (main_table.qty_invoiced - main_table.qty_refunded) - IF(main_table.parent_item_id IS NULL, main_table.base_cost, parent_item.base_cost) * (main_table.qty_invoiced - main_table.qty_refunded) - (@return_fee / 100) * order_totals.order_return_rate - order_totals.order_fee AS profit
    FROM
        sales_flat_order_item AS main_table
    LEFT JOIN sales_flat_order_item AS parent_item ON parent_item.item_id = main_table.parent_item_id
    INNER JOIN sales_flat_order AS orders ON orders.entity_id = main_table.order_id
    INNER JOIN (SELECT DISTINCT
        order_id AS order_id,
            GROUP_CONCAT(DISTINCT sku) AS bought_together,
            SUM(qty_invoiced) AS order_qty_invoiced,
            SUM(qty_refunded) AS order_qty_refunded,
            (SUM(qty_refunded) / SUM(qty_invoiced)) * 100 AS order_return_rate,
            @order_handling_fee / COUNT(DISTINCT item_id) AS order_fee
    FROM
        sales_flat_order_item
    GROUP BY order_id) AS order_totals ON main_table.order_id = order_totals.order_id
    WHERE
        main_table.product_type = 'simple') AS t
WHERE
    t.created_at > DATE_SUB(NOW(), INTERVAL 1 month)
GROUP BY product_id