Query editor
with base_data as (selectdate_trunc('day', ts_source) as date,count(*) as messages,sum(coalesce(usd_value, 0)) as usd_volumefrom layerzero.messageswhere casewhen cast('{{Daily Stats Time Period}}' as varchar) = '7-day' then date_trunc('day', ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '7' daywhen cast('{{Daily Stats Time Period}}' as varchar) = '30-day' then date_trunc('day', ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '30' daywhen cast('{{Daily Stats Time Period}}' as varchar) = '180-day' then date_trunc('day', ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '180' daywhen cast('{{Daily Stats Time Period}}' as varchar) = '365-day' then date_trunc('day', ts_source) > (select max(date(ts_source)) from layerzero.messages) - interval '365' dayelse trueend and date_trunc('day', ts_source) > date '1970-01-01'group by 1), cumulative_data as (selectdate,messages,usd_volume,sum(messages) over (order by date) as cumulative_messages,sum(usd_volume) over (order by date) as cumulative_usd_volume,avg(messages) over (order by date rows between 29 preceding and current row) as messages_30d,avg(usd_volume) over (order by date rows between 29 preceding and current row) as usd_volume_30dfrom base_data) select *from cumulative_dataorder by 1 desc;