nickpayiatis_Untitled Query
    Updated 2022-12-16
    with raw_commit as(
    select tx_sender, block_timestamp,
    try_base64_decode_string(tx_message:txn:note::string)as commit ,
    case when commit ilike '%bnf%' then
    replace( split_part(split_part(split_part(commit,',"',1),':',3),',',1),'"')/ pow(10,6)
    else
    replace( split_part(split_part(split_part(commit,'}',1),':',3),',',1),'"')/ pow(10,6)
    end as commit_amt
    from algorand.core.ez_transaction_payment
    where
    try_base64_decode_string(tx_message:txn:note::string) like 'af/gov1:%'
    and block_timestamp::date between '2022-09-30' and '2022-12-05'

    )

    ,max_commit as(
    select tx_sender, max(block_timestamp) as block_timestamp
    from raw_commit
    where
    commit_amt > 0
    group by tx_sender
    )
    select rv.tx_sender, rv.block_timestamp, rv.commit_amt
    from raw_commit rv
    join max_commit mv on rv.tx_sender = mv.tx_sender and rv.block_timestamp = mv.block_timestamp

    Run a query to Download Data