ray1992

В общем самый приемлемый вариант оказался таким:

SELECT 
  t2.id,
  t2.offer_id,  
  SUM(t2.sum_hold) AS sum_hold,
  SUM(t2.sum_unhold) AS sum_unhold,
  SUM(t2.sum_chargeback) AS sum_chargeback,
  SUM(t2.sum_bonus) AS sum_bonus,
  SUM(t2.earning) AS sum_earning
  FROM   
  (
    SELECT
    `t`.`id`,
    `t`.`offer_id`,
    `t`.`amount`,
    SUM(IF(OtherTransactions.type = 'Hold', OtherTransactions.amount, 0)) AS sum_hold,
    SUM(IF(OtherTransactions.type = 'UnHold', OtherTransactions.amount, 0)) AS sum_unhold,
    SUM(IF(OtherTransactions.type = 'Chargeback', OtherTransactions.amount, 0)) AS sum_chargeback,
    SUM(IF(OtherTransactions.type = 'Bonus', OtherTransactions.amount, 0)) AS sum_bonus,
    t.amount AS earning
    FROM `payments_transactions` `t`
    LEFT OUTER JOIN `payments_transactions` `OtherTransactions`
      ON (`OtherTransactions`.`id_daily_earning` = `t`.`id`)
      AND (OtherTransactions.type = 'Hold'
      OR OtherTransactions.type = 'UnHold'
      OR OtherTransactions.type = 'Chargeback'
      OR OtherTransactions.type = 'Bonus')
    WHERE (t.type = "Daily Earning")
    GROUP BY t.id
)
t2
GROUP BY t2.offer_id
ORDER BY t2.id DESC
LIMIT 30

Сначала джойним холды и пр, считаем их сумму в группировке по t.id. Как заработок берем не сумму amount, а просто amount. Затем оборачиваем это все в еще один запрос, в котором уже группируем как хотим, снова считаем суммы уже в этой группировке и делаем LIMIT.
На 350к записей отрабатывает за ~0.7 сек. Вполне терпимо.