SELECT

chainbase_utils.post(

'/v1/dw/query',

{ 'query': 'select date_trunc(\'week\', block_timestamp) as day_date, count(1) as num_txs, count(CASE WHEN status = 1 THEN 1 END) as success_count, count(CASE WHEN status != 1 THEN 1 END) as ailure_count from zksync.transactions group by 1 order by 1' }

) as resp

),

converter as (

select

f.value as imported_data

from

raws r,

lateral FLATTEN(input => resp:data:data:result) f

),

clean_data as (

select

imported_data:day_date::date as day_date,

imported_data:num_txs::integer as num_txs,

imported_data:success_count::integer as success_count,

imported_data:failure_count::integer as failure_count

from

converter

select

*,

avg(num_txs) over (order by day_date) as average_txs_per_week

from clean_data