ramishoow2023-02-16 03:51 PM
Updated 2023-02-16
99
1
2
3
4
5
6
7
8
9
10
11
›
⌄
with ramishoow as ( select recorded_hour::date as time, id as symbol, avg(close) as "price usd"
from crosschain.core.fact_hourly_prices where recorded_hour between '2023-01-01' and CURRENT_DATE - 1 and id in( 'bitcoin-avalanche-bridged-btc-b')
group by 1,2 ), txns as ( select "price usd", btc_ra * A."price usd" as "amount usd", t.* from ( select raw_amount/1e8 as btc_ra,
tx_hash, block_timestamp, to_address from avalanche.core.fact_token_transfers where 1=1 and origin_function_signature = '0xa888d914'
and origin_from_address = '0xf5163f69f97b221d50347dd79382f11c6401f1a1' order by tx_hash, _LOG_ID ) t join ramishoow A on t.block_timestamp::date = A.time
) select date_trunc('{{date_trunc}}',block_timestamp) as time, count(distinct(tx_hash)) as n_txns, count(distinct(to_address)) as n_wallets,
sum("amount usd") as "Bridged Volume $USD", sum(btc_ra) as swap_volume_btc, sum("Bridged Volume $USD") over (order by time asc rows between unbounded preceding and current row) as "cum swap volume usd",
sum(swap_volume_btc) over (order by time asc rows between unbounded preceding and current row) as "cum swap volume btc",
sum(n_txns) over (order by time asc rows between unbounded preceding and current row) as "Cumulative Number Transactions " from txns group by 1
--from txns group by 1
--
Run a query to Download Data