nsa2000Inbound Bridging
    Updated 2022-11-22
    --this code is thankfully burrowed from 0xHaM☰d: https://app.flipsidecrypto.com/dashboard/fHME6k
    with priceTb as (
    select
    date_trunc('{{Frequency}}', timestamp) as p_date,
    TOKEN_CONTRACT,
    symbol,
    avg(price_usd) as "PRICE($)"
    from flow.core.fact_prices
    WHERE timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'
    AND token != 'Blocto'
    group by 1, 2, 3
    order by 1
    )
    SELECT
    date_trunc('{{Frequency}}', block_timestamp) as date,
    BLOCKCHAIN,
    BRIDGE,
    symbol,
    COUNT(DISTINCT tx_id) as tx_cnt,
    COUNT(DISTINCT FLOW_WALLET_ADDRESS) as bridger_cnt,
    sum(AMOUNT*"PRICE($)") as Vol_usd,
    sum(tx_cnt) over (partition by BLOCKCHAIN order by date) as cum_tx_cnt,
    sum(Vol_usd) over (partition by BLOCKCHAIN order by date) as cum_Vol_usd,
    sum(tx_cnt) over (partition by symbol order by date) as cum_tx_cnt_symbol,
    sum(Vol_usd) over (partition by symbol order by date) as cum_Vol_usd_symbol
    FROM flow.core.ez_bridge_transactions a JOIN priceTb b on a.TOKEN_CONTRACT = b.TOKEN_CONTRACT and a.BLOCK_TIMESTAMP::date = b.p_date
    where block_timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'
    and DIRECTION ilike ('inbound')
    GROUP by 1,2,3,4
    ORDER by 1

    Run a query to Download Data