KaskoazulALGOFI current commitment
Updated 2023-01-03
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
›
⌄
----algofi
--wallet creation
with algofi_wallets as(
select distinct p.receiver as governor
from (
select tx_group_id, sender, tx_message
from algorand.application_call_transaction as a
where app_id = '465814318'
and try_base64_decode_string(tx_message:txn:note::string) = 'Market: mt'
) as a
join algorand.payment_transaction as p on a.tx_group_id = p.tx_group_id
),
--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 algofi_wallets wal
left join algorand.payment_transaction pay
on wal.governor = 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,
Run a query to Download Data