select
    monday as dt
        ,bonus_id
        ,title as "any(title)"
    ,case
       when title like '%Welcome%' then 'welcome'
      else 'other'
    end
        ,round(sumIf(payment,paid_status = 1)) as real_pay
        ,round(sum(payment)) as MAX_pay
        ,sumIf(period_order_count,paid_status = 1) as sum_bonus_order
        ,sumIf(bonus_count,paid_status = 1) as sum_all_ident_order
        ,round(sumIf(payment,paid_status = 1)/sumIf(period_order_count,paid_status = 1)) as AVG_bonus_order
        ,round(sumIf(payment, paid_status = 1)/sumIf(bonus_count,bonus_count = 1),2) as AVG_all_ident_order
        ,round(sum(if(paid_status = 1, payment, 0))/sum(payment/100),2) as share_of_max
        ,round(countIf(paid_status = 1)/count(*),2) as share_idents_of_participant
    ,round(sum(all_ident_order_sum)/sum(all_ident_order_cnt),2) as avg_ident_bill
    ,round((sum(if(paid_status = 1, payment/100, 0))/sum(if(paid_status = 1, bonus_count, null)))/(sum(all_ident_order_sum)/sum(all_ident_order_cnt)),2) as share_of_avg_ident_bill
    ,week_orders.week_order_cnt
    ,round(sum(if(paid_status = 1, payment/100, 0)))/week_orders.week_order_cnt as pay_per_1_order_of_week
from (
        SELECT
                        ident_id, bonus_id, bonus_count,  payment,  period_start_date, period_end_date, title, monday,paid_status,period_order_count
           ,sum(all_ident_sum) as all_ident_order_sum
           ,sum(cnt) as all_ident_order_cnt
                from (
                        SELECT OrderedDate, ident_id, sum(ident_bill) as all_ident_sum, count(*) as cnt
                        from (
                                         SELECT ident as ident_id, ident_bill, id, toDate(OrderedDate) as OrderedDate
                                         FROM etl_import.order_closed_dist
                                           ALL LEFT JOIN etl_import.order_closed_info_dist ocid using id
                                         where status = 'CP' and companyid = 1 and g_type = 0
                                             and (autoget = 3 or current_autoget = 3)
                                             and toDate(OrderedDate) >= toDate(now()) - interval 6 week
                                         )
                        where ident_id in (
                                SELECT distinct
                                    toUInt64(ident_id) as ident_id
                                from mysql()
                                )
                        group by ident_id, OrderedDate
                        order by OrderedDate
                        ) as cnt
                all inner join
                        (
                          SELECT bonus_id, paid_status, payment/100 as payment, bonus_count, title,period_order_count
                                ,toDate(ifNull(period_date_end_dboc, period_date_end)) as period_end_date
                                ,toDate(period_end_date - INTERVAL period day) as period_start_date
                                ,toMonday(period_end_date) as monday
                                ,ident_id
                          from
                              (
                                SELECT
                                  toUInt64(ident_id) as ident_id, period_date_start as period_date_start_dboc, period_date_end as period_date_end_dboc, paid_status, bonus_id, count as bonus_count
                                from mysql()
                              )
                          all inner join (
                                          select  toUInt64(ident_id) as ident_id, bonus_id, title, payment, period_order_count, period_date_start, period_date_end, period
                                          from mysql()
                                          any inner join (select id as bonus_id, period, title from mysql())
                                                using bonus_id
                                          ) as dbs
                                  using ident_id, bonus_id
                          where period_end_date > toDate(now()) - interval 6 week
                                                            )
                      using ident_id
              where period_end_date < toMonday(now()) and OrderedDate between period_start_date and (period_end_date - INTERVAL 1 day)
              group by ident_id, bonus_id, bonus_count,  payment,  period_start_date, period_end_date, title, monday,paid_status,period_order_count
                                )
    all left join (select count(*) as week_order_cnt, monday from etl_import.order_closed_dist
                                        where toDate(OrderedDate) > toDate(now()) - interval 7 week and status = 'CP' and id_client > 0 and companyid = 1 and g_type = 0
                                        group by toMonday(OrderedDate) as monday
                  ) as week_orders using monday
    where monday <= toDate(now()) and toDate(monday) >toDate(now()) - interval 7 week
    group by bonus_id, monday, week_order_cnt,title
    order by dt, bonus_id;