barbodstaking2
Updated 2022-05-25
999
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
›
⌄
With pact_app_ids as(
SELECT
DISTINCT tx_message :txn :apid :: NUMBER AS app_id
FROM
flipside_prod_db.algorand.transactions
WHERE
inner_tx = 'FALSE'
AND tx_message :dt :itx [0] :txn :type :: STRING = 'acfg'
AND tx_message :dt :itx [0] :txn :apar :an :: STRING LIKE '%PACT LP Token'
AND tx_message :dt :itx [0] :txn :apar :au :: STRING = 'https://pact.fi/'
),
pactfi as (
select
block_timestamp::date as date ,
case when asset_name ='ALGO/goETH PACT LP Token' then 'goETH'
when asset_name ilike '%Algo/goBTC%' then 'goBTC'
end as asset,
case when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'ADDLIQ' then 'Mint'
when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'REMLIQ' then 'Burn'
end as type ,
count (DISTINCT sender ) as sender ,
count (tx_group_id) as num_transactions,
sum(asset_amount/1e6) as amount
from flipside_prod_db.algorand.application_call_transaction pact_app_ids join flipside_prod_db.algorand.asset_transfer_transaction using (tx_group_id)
join flipside_prod_db.algorand.asset using (asset_id)
where app_id in (select * from pact_app_ids)
group by 1,2,3 having asset is not null and type is not NULL
),
algofi as (
select block_timestamp::date as date ,
case when app_id =635864509 then 'goETH'
when app_id =635860537 then 'goBTC'
end as asset ,
case when try_base64_decode_string(tx_message:txn:note::string) ='Market: mt' then 'Mint'
when try_base64_decode_string(tx_message:txn:note::string) ='Market: rcu' then 'Burn'
end as type ,
Run a query to Download Data