sql - BIQQUERY - Summing previews 30 days -

i trying sum each date previews 30 days, however, it's not summing in following sql try order date x orders came if date >= date -30days sum(orders of last 30 days) end

select    a.shop_country   ,a.order_date   ,a.order_week   ,sum(a.number_orders) orders   ,case when a.order_date >= date_add(b.order_date, -30, "day") sum(a.number_orders) else 0 end total_orders   [marketing-int:finance_report.overall_view] join [marketing-int:finance_report.overall_view] b   on a.order_date = b.order_date  group   a.shop_country   ,a.order_date   ,b.order_date   ,a.order_week   ,a.order_channel   ,a.number_orders 

try below

select    shop_country   ,order_date   ,order_week   ,sum(number_orders) over(partition shop_country order order_date        range between 29 preceding , current row) total_orders     [marketing-int:finance_report.overall_view] 

hope start you

see window-frame-clause section of window functions more details