KaskoazulGARD current commitment
    Updated 2022-07-08
    ----gard
    --wallet creation
    with gard_wallets as(
    select distinct sender from algorand.transactions where
    TRY_BASE64_DECODE_binary(tx_message :lsig :arg[0]::string )::string = '0000000000000004'
    ),
    --getting all commitments from the created gard wallets
    all_commitments as(
    select block_timestamp, tx_group_id, pay.sender, replace(replace(try_base64_decode_string(tx_message:txn:note::string)::string,'af/gov1:j{"com":')::string,'}')/ pow(10,6) as commit_amount from gard_wallets wal
    left join algorand.payment_transaction pay
    on wal.sender = pay.sender
    where
    try_base64_decode_string(tx_message:txn:note::string)::string like 'af/gov1:j{"com":%'
    and block_timestamp::date > '2022-06-28'
    ),
    --selecting their most recent commitment
    most_recent_commitment as (
    select * from all_commitments
    where block_timestamp in (select max(block_timestamp) from all_commitments group by sender)
    ),
    --filtering out the wallets that have a balance less than the committed amount
    governor_list as (
    select case
    when commit_amount <= acc.balance then 'ELIGIBLE'
    when commit_amount > acc.balance then 'UNDERFUNDED'
    else 'ERROR'
    end as status,
    com.sender as account_address,
    com.block_timestamp::date as registered,
    com.commit_amount as committed_ALGOs,
    acc.balance
    from most_recent_commitment com
    left join algorand.account acc
    on com.sender = acc.address
    ),

    Run a query to Download Data