RamaharHOP OP > ETH
    Updated 2022-10-22
    -- OP > ETH
    with token_price as (select
    hour::date as dt,
    symbol,
    AVG(price) as avg_price
    from optimism.core.fact_hourly_token_prices
    where symbol IN ('USDC', 'USDT', 'DAI', 'HOP', 'WBTC', 'WETH', 'SNX')
    group by 1, 2),
    hops as (select
    BLOCK_TIMESTAMP::date as dayz,
    tx_hash,
    origin_from_address,
    case
    when contract_address = lower('0xa81D244A1814468C734E5b4101F7b9c0c577a8fC') then 'USDC'
    when contract_address = lower('0x46ae9BaB8CEA96610807a275EBD36f8e916b5C61') then 'USDT'
    when contract_address = lower('0x7191061D5d4C60f598214cC6913502184BAddf18') then 'DAI'
    when contract_address = lower('0x83f6244Bd87662118d96D9a6D44f09dffF14b30E') then 'WETH'
    when contract_address = lower('0xB1ea9FeD58a317F81eEEFC18715Dd323FDEf45c4') then 'WBTC'
    when contract_address = lower('0x03D7f750777eC48d39D080b020D83Eb2CB4e3547') then 'HOP'
    when contract_address = lower('0x16284c7323c35F4960540583998C98B1CfC581a7') then 'SNX'
    when contract_address = lower('0x33Fe5bB8DA466dA55a8A32D6ADE2BB104E2C5201') then 'sUSD'
    end as symbol,
    event_inputs:amount as amount,
    CASE
    when symbol IN ('WETH', 'DAI', 'HOP') then amount / POW(10,18)
    when symbol IN ('USDC', 'USDT') then amount / POW(10,6)
    when symbol = 'WBTC' then amount / POW(10,8)
    end as adjusted_amount

    from optimism.core.fact_event_logs l
    --join optimism.core.fact_token_transfers t ON l.tx_hash = t.tx_hash AND l.block_timestamp = t.block_timestamp
    where
    l.contract_address IN ( lower('0xa81D244A1814468C734E5b4101F7b9c0c577a8fC') --USDC 10e6
    ,lower('0x46ae9BaB8CEA96610807a275EBD36f8e916b5C61') --USDT 10e6
    -- ,lower('0x22B1Cbb8D98a01a3B71D034BB899775A76Eb1cc2') --MATIC 10e18 --
    Run a query to Download Data