SQL Test Scripts

Статистика по рекламодателям

Скрипт, возвращающий данные для этой страницы. Использовался для тестирования этого кода. ссылка на redmine

select  sum(show_count) a1, sum(coalesce(click_count, 0)) a2,
        sum(leads_count) a3, sum(sales_count) a4,
        sum(confirmed_revenue * (1 + p_aff.fee / 100.0)) a5,
        sum(not_confirmed_revenue * (1 + p_aff.fee / 100.0)) a6,
        sum(canceled_revenue * (1 + p_aff.fee / 100.0)) a7,
        p.id a8, p.first_name || ' ' || p.last_name || ' (' || coalesce(p.organization, '--') || ')' a9
from offer o
left join offer_stat on offer_stat.creation_time between '2012-05-01' and '2012-06-30' and o.id = offer_stat.master
left join user_profile p on o.user_id = p.id
left join user_profile p_aff on offer_stat.aff_id = p_aff.id
where o.parent_id is null
group by p.id, p.first_name, p.last_name, p.organization order by a2
desc offset 0 limit 20;

Суммарная статистика

Redmine.

select 
  sum(canceled_revenue * (1 + p_aff.fee / 100.0))       canceled,
  sum(not_confirmed_revenue)                            not_confirmed_partner,
  sum(not_confirmed_revenue * (p_aff.fee / 100.0))      not_confirmed_fee,
  sum(not_confirmed_revenue * (1 + p_aff.fee / 100.0))  not_confirmed_sum,
  sum(confirmed_revenue)                                confirmed_partner,
  sum(confirmed_revenue * (p_aff.fee / 100.0))          confirmed_fee,
  sum(confirmed_revenue * (1 + p_aff.fee / 100.0))      confirmed_sum
from
  offer_stat
left join
  user_profile p_aff on offer_stat.aff_id = p_aff.id
where
  creation_time between '2012-06-01' and '2012-06-30';

Сумма выплат

Неподтвержденные с истекшим сроком холда:

select
  sum(e.amount)
from 
  accounting_entry e
left join 
  offer_action oa on oa.id = e.source_id
left join
  offer o on o.id = oa.offer_id
where 
  e.amount > 0
  and e.account_id <> 18
  and e.event = 1
  and oa.state = 0 
  and cast(now() as date) - cast(oa.creation_time as date) > o.hold_days  
  and oa.creation_time between '2012-05-01' and '2012-06-30'
  and oa.id is not null;

Подтвержденные:

select
  sum(confirmed_revenue)
from offer_stat
where
  creation_time between '2012-05-01' and '2012-06-30';

Неподтверждённые с истекшим сроком

select action.id
from offer
left join offer_action action
on action.offer_id = offer.id
where
  cast(now() as date) - cast(action.creation_time as date) > offer.hold_days
  and action.state = 0;

Неподтвержденные опять (но с комиссией)

select
  sum(aff_entry.amount)   as affiliate_sum,
  sum(admin_entry.amount) as fee_sum,
  sum(aff_entry.amount) + sum(admin_entry.amount) as total
from
  offer_action action

left join offer
on offer.id = action.offer_id

left join user_profile affiliate
on affiliate.id = action.aff_id

left join admin_account_not_confirmed admin_not_confirmed
on admin_not_confirmed.id is not null

left join accounting_entry aff_entry
on 
  aff_entry.source_id = action.id
  and aff_entry.event = 1
  and aff_entry.account_id = affiliate.affiliate_account_not_confirmed_id

left join accounting_entry admin_entry
on
  admin_entry.source_id = action.id
  and admin_entry.event = 1
  and admin_entry.account_id = admin_not_confirmed.account_id

where
  action.state = 0
  and cast(now() as date) - cast(action.creation_time as date) > offer.hold_days;

Статистика по оферам. Новая.

select
  *,
  case shows_count
    when 0 then null
    else clicks_count * 100.0 / shows_count
  end ctr,
  case clicks_count
    when 0 then null
    else (leads_count + sales_count) * 100.0 / clicks_count
  end cr,
  case clicks_count
    when 0 then null
    else (confirmed_revenue + not_confirmed_revenue) / clicks_count
  end ecpc,
  case shows_count
    when 0 then null
    else (confirmed_revenue + not_confirmed_revenue) * 1000.0 / shows_count
  end ecpm
from
  (
  select
    sum(show_count)               shows_count,
    sum(coalesce(click_count, 0)) clicks_count,
    sum(leads_count)              leads_count,
    sum(sales_count)              sales_count,
    sum(confirmed_revenue)        confirmed_revenue,
    sum(not_confirmed_revenue)    not_confirmed_revenue,
    sum(canceled_revenue)         canceled_revenue, 
    /* + select + */
    o.id offer_id
  from
    offer o
  left join
    offer_stat
    on offer_stat.creation_time between '2012-06-01' and '2012-06-10'
    and o.id = offer_stat.master
  where
    o.parent_id is null
  group by
    o.id, o.name
  /* " + groupBy + " */
  ) as sums
order by
/*" + orderBy + " desc */
  clicks_count desc
offset 0 limit 20;

Суммарное количество показов\кликов\лидов\продаж.

select
  extract(month from creation_time) as month,
  sum(show_count)  as shows,
  sum(click_count) as clicks,
  sum(sales_count) as sales,
  sum(leads_count) as leads
from offer_stat
group by month
order by month;

Подсчёт mlm

MLM с разбивкой по реферрерам. Использовался тут.

select
    sum(dst.amount * 0.07 /*mlm rate*/) amount,
    referrer.id, referrer.first_name || ' ' || referrer.last_name
from offer_action action

left join admin_account admin_acc
on admin_acc.id is not null

left join user_profile affiliate
on affiliate.id = action.aff_id

left join user_profile referrer
on referrer.id = affiliate.referrer

left join accounting_entry dst
on
  dst.event = 2 /*action_approved*/
  and dst.source_id = action.id
  and dst.amount > 0
  and dst.account_id = admin_acc.account_id

where
  action.state = 1 /* action approved */
  and referrer.id is not null
group by referrer.id, referrer.first_name, referrer.last_name
order by amount desc;

Количество зарегистрировавшихся партнёров по месяцам

select
  extract(month from register_time) as month,
  count(*)                  
from user_profile
where affiliate_account_id is not null            
group by month             
order by month;

Количество зарегистрировавшихся рекламодателей по месяцам

select
  extract(month from register_time) as month,
  count(*)                  
from user_profile
where advertiser_account_id is not null            
group by month             
order by month;