rtb_balance_by_campaigns_and_sites.sql

select * from (
  select expenses.*, revenue.sum, 100 * (revenue.count + 1)/ (expenses.count + 1) as ctr, (revenue.sum - expenses.sum) as profit  from (
    select
    campaign_id,
    site_id,
    count(id),
    sum(bid)/1000 as sum
    from winning_bids
    where created_at > now() - interval '1 hour'
    group by campaign_id,site_id
    order by sum desc
  ) as expenses
  left join (
    select
    campaign_id,
    site_id,
    count(id),
    sum(amount)/1000 as sum
    from trackings
    where created_at > now() - interval '1 hour'
    group by campaign_id, site_id
    order by sum desc
  ) as revenue
  on expenses.campaign_id = revenue.campaign_id and expenses.site_id = revenue.site_id
  order by expenses.sum desc
) as T;

rtb_winnings_vs_losses.sql

select * from (
  select beta.*, alpha.count, alpha.avg, (100*alpha.count / (alpha.count + beta.count)) from (
    select 
    campaign_id,
    site_id,
    count(id),
    round(avg(bid)) as avg
    from fill_forfeits
    where created_at > now() - interval '1 hour'
    group by campaign_id, site_id
  ) as beta
  left join (
    select 
    campaign_id,
    site_id,
    count(id),
    round(avg(bid)) as avg
    from winning_bids
    where created_at > now() - interval '1 hour'
    group by campaign_id,site_id
  ) as alpha
  on alpha.campaign_id = beta.campaign_id and alpha.site_id = beta.site_id
  where beta.count is not null
  order by beta.count desc
) as T;