lagandispenserThe number of depositors, borrow and redeemers over time
Updated 2022-07-02
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
›
⌄
select
block_timestamp::date as date ,
case when APP_ID = 686498781 then 'ALGO'
when APP_ID = 686500029 then 'USDC'
when APP_ID = 686500844 then 'USDt'
when APP_ID = 686501760 then 'goBTC'
when APP_ID = 694405065 then 'goETH'
when APP_ID = 694464549 then 'gALGO3'
when APP_ID = 743679535 then 'ALGO/gALGO3 TMP1.1'
when APP_ID = 743685742 then 'ALGO/gALGO3 PLP'
when APP_ID = 747239433 then 'ALGO/USDC PLP'
when APP_ID = 747237154 then 'ALGO/USDC TMP1.1'
end as pool,
case when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'd' then 'deposit'
when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'r' then 'redeem'
when TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'b' then 'borrow'
end as type ,
count( DISTINCT tx_group_id) as num_transactions,
count(DISTINCT sender ) as senders ,
sum (ASSET_AMOUNT) as amount,
sum (amount) over (partition by pool,type order by date ) as cum_amount
from flipside_prod_db.algorand.application_call_transaction join flipside_prod_db.algorand.asset_transfer_transaction using (tx_group_id)
where APP_ID in (686498781,686500029,686500844,686501760,694405065,694464549,743679535,743685742,747237154,747239433) and ASSET_AMOUNT >0
group by 1,2,3 having pool is not null and type is not null
Run a query to Download Data