KaskoazulGARD current commitment
Updated 2022-07-08
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
----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