nitsNet deposits over time
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
›
⌄
with base as (select
tx_group_id
from algorand.application_call_transaction
where
TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'd'),
base2 as (select tx_group_id
from algorand.application_call_transaction
where
TRY_BASE64_DECODE_STRING(tx_message :txn :apaa [0] :: STRING) = 'r'),
base3 as (select date_trunc('day', block_timestamp) as day,
a.asset_id,
asset_name,
sum(amount) as amount_deposited
from algorand.transfers a
join algorand.asset b
on a.asset_id = b.asset_id
where tx_group_id in (select tx_group_id from base)
and inner_tx = 'FALSE'
group by 1,2,3),
base4 as (select date_trunc('day', block_timestamp) as day,
a.asset_id,
asset_name,
sum(amount) as amount_redeemed
--sum(amount_redeemed) over (partition by asset_name order by day) as cumulative_amount_redeemed,
from algorand.transfers a
join algorand.asset b
on a.asset_id = b.asset_id
where tx_group_id in (select tx_group_id from base2)
and inner_tx = 'TRUE'
group by 1,2,3),
base5 as (select asset_id,
avg(price_usd) as price_usdz
from flipside_prod_db.algorand.prices_pool_balances
Run a query to Download Data