with lp_create_tx as (
select
date_trunc('day', block_timestamp) as blocktime,
count(distinct tx_group_id) as number_of_pools
from algorand.application_call_transaction
inner join algorand.block on (algorand.block.block_id = algorand.application_call_transaction.block_id)
where tx_message:txn:apaa[0]::string = 'Ym9vdHN0cmFw'
and tx_message:txn:apaa[1] is not null
and tx_message:txn:apaa[2] is not null
and app_id = 552635992
group by blocktime
)
select
*,
sum(number_of_pools) over (order by blocktime) as total_pools
from lp_create_tx