select userid ,count(case when orderdate >= '2020-07-15' and orderdate <= '2020-07-16' then orderid else null end) cnt_1d ,sum(case when orderdate >= '2020-07-15' and orderdate <= '2020-07-16' then amount else 0 end) amt_1d ,count(case when orderdate >= '2020-07-09' and orderdate <= '2020-07-16' then orderid else null end) cnt_7d ,sum(case when orderdate >= '2020-07-09' and orderdate <= '2020-07-16' then amount else 0 end) amt_7d ,count(case when orderdate >= '2020-07-01' and orderdate <= '2020-07-16' then orderid else null end) cnt_15d ,sum(case when orderdate >= '2020-07-01' and orderdate <= '2020-07-16' then amount else 0 end) amt_15d from edw_htl_order group by userid