SELECT
    Q.*,
    Q.selling_amount / NULLIF(Q.order_stock, 0) AS avg_selling_price,
    Q.selling_amount / NULLIF(Q.order_count, 0) AS avg_order_amount,
    COALESCE((Q.selling_amount - Q.supplier_amount) * 100. / NULLIF(Q.selling_amount, 0),0) AS percent_order_margin_amount,
    Q.art_sold_before_cancels * 100. / NULLIF(Q.art_count, 0) AS percent_art_sold_before_cancels,
    Q.art_sold * 100. / NULLIF(Q.art_count, 0) AS percent_art_sold
FROM (
    SELECT
        C.pp_campaign_id,
        C.description || ' ' || C.pp_campaign_id AS campaign_name,
        C.url,
        C.start_at,
        C.finish_at,
        C.art_count,

        COALESCE(SUM(DISTINCT(C.data->'stock_count_by_supplier'->>ORG.ref1c::text)::int), 0) AS stock_count_by_supplier,
        COALESCE(SUM(DISTINCT(C.data->'art_count_by_supplier'->>ORG.ref1c::text)::int), 0) AS art_count_by_supplier,
        COALESCE(COUNT(DISTINCT OI.pp_product_id::bigint << 32 + OI.pp_color_id) FILTER (WHERE NOT OI.is_canceled), 0) AS art_sold,
        COUNT(DISTINCT OI.pp_product_id::bigint << 32 + OI.pp_color_id) AS art_sold_before_cancels,
        COALESCE(SUM(OI.current_quantity) FILTER (WHERE NOT OI.is_canceled), 0) order_stock,
        COALESCE(COUNT(DISTINCT OI.order_id) FILTER (WHERE NOT OI.is_canceled), 0) AS order_count,
        COALESCE(SUM(OI.current_quantity * OI.supplier_price) FILTER (WHERE NOT OI.is_canceled), 0) supplier_amount,
        COALESCE(SUM(OI.current_quantity * OI.new_price) FILTER (WHERE NOT OI.is_canceled), 0) AS selling_amount,

        SUM(OI.quantity * OI.new_price) AS selling_amount_before_cancels,
        (SELECT SUM(delivery_cost) FROM orders WHERE id = ANY(ARRAY_AGG(DISTINCT OI.order_id))) AS delivery_amount,

        array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT ORG.name ), NULL), ', ') AS org_names,
        array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT OW.name), NULL), ', ') AS owner_names,
        array_to_string(ARRAY_REMOVE(ARRAY_AGG(DISTINCT U.name ), NULL), ', ') AS buyer_names
    FROM campaign C
	LEFT JOIN orderitem OI ON OI.pp_campaign_id = C.pp_campaign_id														
    LEFT JOIN batch B ON B.pp_batch_id = OI.pp_batch_id
    INNER JOIN supplier_contract CC ON CC.ref1c = OI.contract_ref1c
    INNER JOIN supplier_org ORG ON ORG.id = CC.org_id
    LEFT JOIN users U on U.ref1c = B.buyer_owner_ref1c
    LEFT JOIN owner OW ON OW.ref1c = CC.owner_id
    WHERE C.start_at >= '2018-08-17'::date AND C.finish_at < '2018-08-31'::date + 1
        AND (NULL::int[] IS NULL OR ORG.supplier_id = ANY(NULL::int[]))
        AND (NULL::text IS NULL OR C.description ILIKE '%' || NULL::text || '%')
        AND (NULL::int IS NULL OR C.pp_campaign_id = NULL::int)
        AND (NULL::uuid[] IS NULL OR C.suppliers && (NULL::uuid[]))
        AND (NULL::uuid[] IS NULL OR B.buyer_owner_ref1c = ANY(NULL::uuid[]))
    GROUP BY C.id
) Q