SELECT
    sum(amount_to_burn)
FROM
(
    SELECT
        spoy.referer_id,
        spoy.amount before_one_year,
        IFNULL(saly.amount, 0) minus_after_one_year,
        spoy.amount + IFNULL(saly.amount, 0) amount_to_burn
    FROM
        (
            SELECT
                referer_id, SUM(amount) amount
            FROM
                referral_balance_ops
            LEFT JOIN
                users
            ON
                users.user_id=referral_balance_ops.referer_id
            LEFT JOIN
                user_contacts
            ON
                user_contacts.user_id = users.user_id
            WHERE
                opdate < '2020-11-06' - INTERVAL 1 YEAR
            AND
                NOT FIND_IN_SET('is_entrepreneur', flags)
            AND
                user_contacts.fldid='code'
            AND
                user_contacts.grpid != 'ru_org'
            GROUP BY 1
            HAVING amount > 0
        )
        spoy
    LEFT JOIN
        (
            SELECT
                referer_id, SUM(amount) amount
            FROM
                referral_balance_ops
            LEFT JOIN
                users
            ON
                users.user_id=referral_balance_ops.referer_id
            LEFT JOIN
                user_contacts
            ON
                user_contacts.user_id = users.user_id
            WHERE
                opdate > '2020-11-06' - INTERVAL 1 YEAR
            AND
                opdate < '2020-10-27'
            AND
                optype IN ('transfer_bonus', 'payout_bonus', 'burn_bonus')
            AND
                NOT FIND_IN_SET('is_entrepreneur', flags)
            AND
                user_contacts.fldid='code'
            AND
                user_contacts.grpid != 'ru_org'
            GROUP BY 1
        )
        saly ON spoy.referer_id = saly.referer_id
    GROUP BY
        1
    HAVING
        amount_to_burn > 0
    ORDER BY
        1
)
agg_table;