ramishoow2023-02-16 03:51 PM
    Updated 2023-02-16
    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