prices AS (

SELECT

DATE_TRUNC('HOUR', HOUR) AS HOUR

, TOKEN_ADDRESS

, CASE TOKEN_ADDRESS

WHEN '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN 'ETH' --WETH18DECIMALS

WHEN '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 'USDC' --USDC6DECIMALS

WHEN '0x6b175474e89094c44da98b954eedeac495271d0f' THEN 'DAI' --DAI18DECIMALS

WHEN '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN 'USDT' --USDT6DECIMALS

WHEN '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN 'WBTC' --WBTC8DECIMALS

WHEN '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0' THEN 'wstETH'--wstETH18DECIMALS

WHEN '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0' THEN 'LORDS'--LORDS18DECIMALS

END AS tokenSymbol

, CASE TOKEN_ADDRESS

WHEN '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN 18 --WETH18DECIMALS

WHEN '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN 6 --USDC6DECIMALS

WHEN '0x6b175474e89094c44da98b954eedeac495271d0f' THEN 18 --DAI18DECIMALS

WHEN '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN 6 --USDT6DECIMALS

WHEN '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' THEN 8 --WBTC8DECIMALS

WHEN '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0' THEN 18 --wstETH18DECIMALS

WHEN '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0' THEN 18 --LORDS18DECIMALS

END AS decimals

, AVG(price) AS price

FROM crosschain.core.fact_hourly_prices

WHERE BLOCKCHAIN = 'ethereum'

AND HOUR > '2022-11-22'

AND TOKEN_ADDRESS IN (

'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETH18DECIMALS

, '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --USDC6DECIMALS

, '0x6b175474e89094c44da98b954eedeac495271d0f' --DAI18DECIMALS

, '0xdac17f958d2ee523a2206206994597c13d831ec7' --USDT6DECIMALS

, '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' --WBTC8DECIMALS

, '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0' --wstETH18DECIMALS

, '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0' --LORDS18DECIMALS