-- 运营
select :date as date,
       realname as user_name,
       count as account_num,
       cost,
       concat(round((cost-yes_week_cost)/yes_week_cost*100,2),'%') as cost_rate,
       effect_num,
       new_campaign_num,
       new_ad_num,
       quarter_cost
from (
    select user_id,count(1) as count,sum(cost) as cost from (
    select user_id, account_id, sum(cost) as cost
      from application.bytedance_advertiser_report_daily_dw
      where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
        and cost > 0
    and company_id='d57fecdcf7a94d009736d9c850731582'
    and user_id in(
        select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
        union all
        select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
          )
    group by user_id,account_id)t
    group by user_id) a
         left join (
    select user_id,
           sum(cost) as yes_week_cost
    from application.bytedance_advertiser_report_daily_dw
    where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
      and cost > 0
    group by user_id
) b
                   on a.user_id = b.user_id
         left join (
    select user_id,
           sum(new_effect_num)       as effect_num,
           sum(new_campaign_num) as new_campaign_num,
           sum(new_ad_num)       as new_ad_num
    from application.app_bytedance_account_base_info_d
    where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
    group by user_id
) c
                   on a.user_id = c.user_id
         left join (
    select user_id,
           sum(cost) as quarter_cost
    from application.app_bytedance_advertiser_report_busi_m
    where stat_mon between case
                               when quarter(date_sub(now(), interval 1 day)) = 1 then 202201
                               when quarter(date_sub(now(), interval 1 day)) = 2 then 202204
                               when quarter(date_sub(now(), interval 1 day)) = 3 then 202207
                               when quarter(date_sub(now(), interval 1 day)) = 4 then 202210
        end
              and
              case
                  when quarter(date_sub(now(), interval 1 day)) = 1 then 202203
                  when quarter(date_sub(now(), interval 1 day)) = 2 then 202206
                  when quarter(date_sub(now(), interval 1 day)) = 3 then 202209
                  when quarter(date_sub(now(), interval 1 day)) = 4 then 202212
                  end
    group by user_id
) d
                   on a.user_id = d.user_id
left join `jeecg-boot`.sys_user user
on a.user_id=user.id


-- 项目

select :date                                                                as date,
       project_name,
       account_count,
       user_count,
       a.cost,
       concat(round(ifnull((a.cost - c.cost) / (c.cost) * 100, 2), 0), '%') as cost_rate,
       d.tb_rate,
       e.hot_num

from (select project_id, project_name, count(1) as account_count, sum(cost) as cost
      from (
               select project_id, project_name, account_id, sum(cost) as cost
               from application.bytedance_advertiser_report_daily_dw
               where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
                 and cost > 0
                 and company_id = 'd57fecdcf7a94d009736d9c850731582'
                   and user_id in(
        select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
        union all
        select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
          )
               group by project_id, account_id) t
      group by project_id) a
         left join
     (select project_id, count(1) as user_count
      from (
               select project_id, user_id
               from `jeecg-boot`.ctop_user_allocation
               group by project_id, user_id) t
      group by project_id) b
     on a.project_id = b.project_id
         left join(
    select project_id, sum(cost) as cost
    from application.bytedance_advertiser_report_daily_dw
    where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
      and cost > 0
      and company_id = 'd57fecdcf7a94d009736d9c850731582'
    group by project_id
) c
                  on a.project_id = c.project_id
         left join (
    select a.project_id, concat(round((sum(a.cost) - sum(b.cost)) / sum(b.cost) * 100, 2), '%') as tb_rate
    from application.bytedance_advertiser_report_daily_dw a
             left join application.bytedance_advertiser_report_daily_dw b
                       on a.project_id = b.project_id
    where a.stat_datetime = date_format(:date, '%Y%m%d')
      and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
    group by a.project_id,b.project_id
) d
                   on a.project_id = d.project_id
         left join (
    select project_id, sum(new_hot_num) as hot_num
    from application.app_bytedance_account_base_info_d
    where stat_date between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
    group by project_id
) e
                   on a.project_id = e.project_id



-- 账户


select :date as date,
       account_name,
       a.account_id,
       realname,
       cost,
       c.cost_rate,
       b.tb_rate
from (select account_id, account_name, user_id, cost
      from application.bytedance_advertiser_report_daily_dw
      where stat_datetime = date_format(:date, '%Y%m%d')
        and cost > 0
        and company_id = 'd57fecdcf7a94d009736d9c850731582'
        and user_id in(
        select id from `jeecg-boot`.sys_user where leader_id='c52551687c524a96a3904658a35f0f26'
        union all
        select id from `jeecg-boot`.sys_user where id='c52551687c524a96a3904658a35f0f26'
          )) a
         left join (
    select a.account_id, concat(round((a.cost - b.cost) / b.cost * 100, 2), '%') as tb_rate
    from application.bytedance_advertiser_report_daily_dw a
             left join application.bytedance_advertiser_report_daily_dw b
                       on a.account_id = b.account_id
    where a.stat_datetime = date_format(:date, '%Y%m%d')
      and b.stat_datetime = date_format(date_sub(:date, interval 6 day), '%Y%m%d')
) b
                   on a.account_id = b.account_id
         left join(
    select t.account_id, concat(round((t.cost - t1.yes_week_cost) / t1.yes_week_cost * 100, 2), '%') as cost_rate
    from (select account_id, sum(cost) as cost
          from application.bytedance_advertiser_report_daily_dw
          where stat_datetime between date_format(date_sub(:date, interval 6 day), '%Y%m%d') and date_format(:date, '%Y%m%d')
            and cost > 0
          group by account_id) t
             left join
         (select account_id, sum(cost) as yes_week_cost
          from application.bytedance_advertiser_report_daily_dw
          where stat_datetime between date_format(date_sub(:date, interval 13 day), '%Y%m%d') and date_format(date_sub(:date, interval 7 day), '%Y%m%d')
            and cost > 0
          group by account_id) t1
         on t.account_id = t1.account_id
) c
                  on a.account_id = c.account_id
         left join `jeecg-boot`.sys_user user
                   on a.user_id = user.id
