with gov_info as (

select

date_trunc('day',block_timestamp) as dt,

try_base64_decode_string(tx_message :txn :note) as note,

regexp_replace(note, '^af/gov1:j{"com":|}$|,.*') as commit_amount,

block_id,

sender,

amount,

receiver,

tx_id,

asset_id,

tx_type,

tx_type_name

from

algorand.payment_transaction

where

note like '%af/gov1%'

and block_id >= 18224460 and block_id<= 18502150-- date of 2nd governance period

and note like '%af/gov1:j{"com":%'

and note not like '%af/gov1:j{"com":{%'

and note not like '%bnf%'

and len(note) = position('}', note,1)

),

account_info as (

select

address,

balance

from

algorand.account

dt as day,

gov_info.sender,

gov_info.note,

account_info.balance,