nickpayiatis_Untitled Query
Updated 2022-12-16
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
›
⌄
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