bonus-2011-05-02.sql

select orders.user_id,
  email,
  sum(stake) as mises_perdantes,
  5.00 as bonus
from bets
  join orders on orders.id = bets.order_id
  join users on orders.user_id = users.id
  join races on races.id = bets.race_id
where races.start_time::date BETWEEN '2011-04-29' AND '2011-04-30'
  and bets.status = 'paid'
  and cancelled_by is null
  and bets.bet_type_id = 9 -- bet5
  and win = 0
  group by orders.user_id, users.email having sum(stake) >= 20;

INSERT INTO exceptional_bonus (money_account_id, amount, reason, created_at, updated_at)
SELECT money_accounts.id, 5.00, 'Operation 29-30 avril 2011', now(), now()
 from bets
  join orders on orders.id = bets.order_id
  join users on orders.user_id = users.id
  join money_accounts on money_accounts.user_id = users.id and orders.currency_code = money_accounts.currency_code
where bets.start_time::date BETWEEN '2011-04-29' AND '2011-04-30')
  and bets.status = 'paid'
  and cancelled_by is null
  and bets.bet_type_id = 9 -- bet5
  and win = 0
  group by money_accounts.id having sum(stake) >= 20;