rajsWhite Eth Price
    Updated 2022-08-29
    with prices as
    (
    SELECT
    *
    from ethereum.core.fact_hourly_token_prices
    where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    -- order by 1 desc
    -- limit 10
    )

    SELECT
    -- distinct symbol_in,
    -- distinct symbol_in
    date_trunc('hour', block_timestamp) as date,
    -- sum(case when symbol_in = 'WETH' then amount_in * price end) as buy_amt_usd,
    -- sum(case when symbol_out = 'WETH' then amount_out * price end) as sell_amt_usd
    avg(case when symbol_in = 'WETH' then amount_in * p.price / amount_out * 1000000
    when symbol_out = 'WETH' then amount_out * p.price / amount_in * 1000000 end) as white_price,
    avg(p.price) as eth_price
    -- case when symbol_in = 'WETH' then amount_in * price / amount_out else amount_out * price / amount_in end as price,
    -- *
    from ethereum.core.ez_dex_swaps s
    left join prices p
    on date_trunc('hour', s.block_timestamp) = p.hour
    where contract_address = '0xa51f018a6c9815cd6756d2b2ddf1bac9d003149d'
    -- where token_in = '0xfe4beb9217cddf2422d4bd65449b76d807b30fe1'
    group by 1
    order by 1 desc
    -- limit 100
    Run a query to Download Data