flipside

    Sol price
    Pmisha-bmlMdxSol price
    Updated 2022-06-06
    --sol price
    with t1 as (
    SELECT
    date(HOUR) as dt1,
    avg(PRICE) as price
    from ethereum.core.fact_hourly_token_prices p
    where SYMBOL='USDC'
    and dt1>=CURRENT_DATE-45
    group by 1
    ),

    t2 as (select
    date_trunc('day',BLOCK_TIMESTAMP) as dt2,
    sum(SWAP_TO_AMOUNT)/sum(SWAP_FROM_AMOUNT) as usdc
    from flipside_prod_db.solana.fact_swaps
    where SUCCEEDED='TRUE'
    and SWAP_FROM_MINT='So11111111111111111111111111111111111111112'
    and SWAP_TO_MINT='EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
    and BLOCK_TIMESTAMP>=CURRENT_DATE-45
    group by 1)

    select
    t2.dt2 as dt,
    usdc,
    price,
    usdc*price as sol_price
    from t2 join t1 on t2.dt2=t1.dt1
    Run a query to Download Data