from algorand.account

where account_closed='FALSE'),

gov1 as (select *

from algorand.payment_transaction

where receiver = '57QZ4S7YHTWPRAM3DQ2MLNSVLAQB7DTK4D7SUNRIEFMRGOU7DMYFGF55BY'),

comits as (select *,substring(vote,8) as xyz,substring(xyz,1,len(xyz)-1) as committed_amount

from

(select * ,substring(TRY_BASE64_DECODE_STRING(tx_message:txn:note),10 ) as vote from gov1

where contains(vote, 'com')))

, all_votes as

(select *, case when contains(vote, 'a') then 'a' else 'b' end as voted_at

from

(select * ,substring(TRY_BASE64_DECODE_STRING(tx_message:txn:note),10 ) as vote from gov1

where not contains(vote, 'com') and vote is not NULL)

where contains(vote, '5'))

SELECT date(block_timestamp) as day, case when sum(TRY_CAST(committed_amount as INTEGER))/pow(10,6)> pow(10,9) then sum(TRY_CAST(committed_amount as INTEGER))/pow(10,12) else sum(TRY_CAST(committed_amount as INTEGER))/pow(10,6) end as total_comitted, sum(total_comitted) over (order by day) as commitments_over_time from

(SELECT * from (select sender as s , max(committed_amount) as final_committment from comits

GROUP by 1)

inner join comits

on s= sender and committed_amount = final_committment)

where day < CURRENT_DATE -20

GROUP by 1

ORDER by 1

limit 100