select project_name,thrteenday.signature,thrteenday.video_name,photo_url, sum30charge, sum3charge
from (select a.signature, video_name, round(sum(charge), 2) as sum30charge,photo_url,project_name
      from (select signature, frast_cost_time
            from application.material_farst_cost_time
            where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
           and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
               left join(
          select stat_date, signature, video_name,photo_url,project_name, sum(charge) as charge
          from application.kuaishou_material_video_report_daily_dw
          where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
          group by signature, video_name, stat_date
      ) b
                        on a.signature = b.signature
      where stat_date between  frast_cost_time and date_format(date_sub(frast_cost_time, interval -30 day), '%Y%m%d')
      group by a.signature) thrteenday
         left join(
    select a.signature, video_name, round(sum(charge), 2) as sum3charge
    from (select signature, frast_cost_time
          from application.material_farst_cost_time
          where frast_cost_time >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
        and frast_cost_time<=date_format(date_sub(20220706, interval 3 day), '%Y%m%d')) a
             left join(
        select stat_date, signature, video_name, sum(charge) as charge
        from application.kuaishou_material_video_report_daily_dw
        where stat_date >= date_format(date_sub(20220706, interval 30 day), '%Y%m%d')
        group by signature, video_name, stat_date
    ) b
                      on a.signature = b.signature
    where stat_date between  frast_cost_time and date_format(date_sub(frast_cost_time, interval -3 day), '%Y%m%d')
    group by a.signature
) thrnday
                  on thrteenday.signature = thrnday.signature
where sum30charge = sum3charge