SELECT DATE_TRUNC('DAY', hour) as day,

CASE WHEN symbol = 'WETH' OR symbol = 'ETH' THEN '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'

WHEN symbol = 'WBTC' THEN '0x1bfd67037b42cf73acf2047067bd4f2c47d9bfd6'

WHEN symbol = 'DAI' THEN '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063'

WHEN symbol = 'USDC' THEN '0x2791bca1f2de4661ed88a30c99a7a9449aa84174'

WHEN symbol = 'USDT' THEN '0xc2132d05d31c914a87c6611c10748aeb04b58e8f'

WHEN symbol = 'FRAX' THEN '0x45c32fa6df82ead1e2ef74d17b76547eddfaff89'

WHEN symbol = 'gOHM' THEN '0xd8cA34fd379d9ca3C6Ee3b3905678320F5b45195'

WHEN symbol = 'SDT' THEN '0x361a5a4993493ce00f61c32d4ecca5512b82ce90'

END as token_address, symbol,

AVG(price) / AVG(POWER(10, decimals)) as price_multiplier

FROM optimism.core.fact_hourly_token_prices

GROUP BY day, token_address, symbol

),

arbitrum_prices AS (

SELECT DATE_TRUNC('DAY', hour) as day,

CASE WHEN symbol = 'WETH' OR symbol = 'ETH' THEN '0x82af49447d8a07e3bd95bd0d56f35241523fbab1'

WHEN symbol = 'WBTC' THEN '0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f'

WHEN symbol = 'DAI' THEN '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1'

WHEN symbol = 'USDC' THEN '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8'

WHEN symbol = 'USDT' THEN '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9'

WHEN symbol = 'FRAX' THEN '0x17fc002b466eec40dae837fc4be5c67993ddbd6f'

WHEN symbol = 'gOHM' THEN '0x8d9ba570d6cb60c7e3e0f31343efe75ab8e65fb1'

WHEN symbol = 'SDT' THEN '0x7ba4a00d54a07461d9db2aef539e91409943adc9'

END as token_address, symbol,

AVG(price) / AVG(POWER(10, decimals)) as price_multiplier

FROM optimism.core.fact_hourly_token_prices

GROUP BY day, token_address, symbol

),

binance_prices AS ( -- double check these as binance has their own versions

SELECT DATE_TRUNC('DAY', hour) as day,

CASE WHEN symbol = 'DAI' THEN '0x1af3f329e8be154074d8769d1ffa4ee058b1dbc3'

WHEN symbol = 'USDC' THEN '0x8ac76a51cc950d9822d68b83fe1ad97b32cd580d'