CREATE TABLE dimension_date (
  id int,
  dateid int,
  completedate string,
  daynumberinweek tinyint,
  dayfullname string,
  daynumberinmonth tinyint,
  daynumberinyear int,
  weeknumberinyear tinyint,
  monthnumberinyear tinyint,
  monthfullname string,
  quarternumber tinyint,
  quartername string,
  yearnumber int,
  weekstartdate string,
  weekstartdateid int,
  monthstartdate string,
  monthstartdateid int);


explain
 with daily as (
 select * 
 from dimension_date
 where dateid = 20200228
 ),
 weekly as (
 select dly.dateid,count(1) as mb_wk
 from dimension_date dly
 left join dimension_date wk
 ON datediff(dly.completedate, wk.completedate) >= 0 
 AND datediff(dly.completedate, wk.completedate) < 6
 GROUP BY dly.dateid
 ),
 monthly as (
 select dly.dateid,count(1) as nb_monthly
 from dimension_date dly
 left join dimension_date wk
 ON datediff(dly.completedate, wk.completedate) >= 0 
 AND datediff(dly.completedate, wk.completedate) < 28
 GROUP BY dly.dateid
 )
 select daily.dateid,mb_wk,nb_monthly
 from daily
 left join weekly on daily.dateid = weekly.dateid
 left join monthly on daily.dateid = monthly.dateid;