Mityblocto-in
    Updated 2022-11-08
    with act1 as (
    with tab1 as(
    select BLOCK_TIMESTAMP::date as date,TOKEN_CONTRACT,sum(AMOUNT)as bridge_amount
    from flow.core.fact_bridge_transactions
    where BRIDGE='blocto'
    and DIRECTION='inbound'
    group by 1,2),
    tab2 as (
    select EVENT_CONTRACT,CONTRACT_NAME
    from flow.core.dim_contract_labels)
    select date,CONTRACT_NAME,bridge_amount
    from tab1
    left join tab2
    on tab1.TOKEN_CONTRACT=tab2.EVENT_CONTRACT
    group by 1,2,3),
    act2 as (
    select date_trunc('day',TIMESTAMP)as day,TOKEN,avg(PRICE_USD)as price_usd
    from flow.core.fact_prices
    where TOKEN='REVV' or TOKEN='Flow' or TOKEN='Starly'
    or TOKEN='Blocto' or TOKEN='Sportium' or TOKEN='Rally'
    group by 1,2)
    select day,TOKEN,(bridge_amount*price_usd)as bridge_usd
    from act1
    left join act2
    on act1.date=act2.DAY
    where token is not null
    group by 1,2,3
    Run a query to Download Data