Updated 2023-03-16
    with price as (select
    date_trunc('day',RECORDED_HOUR) as date,
    case
    when id='bitcoin-avalanche-bridged-btc-b' then 'BTC.b'
    when id='ethereum' then 'nETH'
    when id='avalanche-2' then 'WAVAX'
    when id='usd-coin' then 'nUSD'
    when id='defi-kingdoms' then 'JEWEL'
    end as token,
    avg(close) as price
    from crosschain.core.fact_hourly_prices
    group by 1,2
    ),main as(select
    date_trunc('day',a.block_timestamp) as date,
    symbol,
    origin_from_address as user,
    a.tx_hash,
    case when event_name='TokenDeposit' then event_inputs:amount*price/pow(10,DECIMALS) else 0 end as inflow_volume,
    case when event_name='TokenRedeem' then event_inputs:amount*price*(-1)/pow(10,DECIMALS) else 0 end as outflow_volume
    from avalanche.core.fact_transactions a join avalanche.core.fact_event_logs b on a.tx_hash = b.tx_hash
    join avalanche.core.dim_contracts c on ADDRESS = event_inputs:token
    join price d on a.block_timestamp::date = d.date and symbol = token
    where a.tx_hash in (select tx_hash from avalanche.core.fact_token_transfers a join avalanche.core.dim_contracts b on a.contract_address = b.address
    )
    AND event_name in ('TokenDeposit','TokenRedeem')
    and event_inputs:chainId = '53935'
    )
    select
    date,
    symbol,
    count(DISTINCT user) as user_count,
    count(tx_hash) as tx_count,
    sum(inflow_volume) as in_flow_volume,
    sum(outflow_volume) as out_flow_volume,
    sum(inflow_volume) + sum(outflow_volume) as net_flow_volume
    from main
    Run a query to Download Data