WITH E_price AS (

SELECT 'dot' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = '0x7884f51dc1410387371ce61747cb6264e1daee0b' GROUP BY 1,2 -- Binance DOT

UNION

SELECT 'axl' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0x467719aD09025FcC6cF6F8311755809d45a5E5f3') GROUP BY 1,2

UNION

SELECT 'wbtc' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599') GROUP BY 1,2

UNION

SELECT 'weth' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') GROUP BY 1,2

UNION

SELECT 'mkr' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0x9f8F72aA9304c8B593d555F12eF6589cC3A579A2') GROUP BY 1,2

UNION

SELECT 'usdc' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') GROUP BY 1,2

SELECT 'usdt' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0xdAC17F958D2ee523a2206206994597C13D831ec7') GROUP BY 1,2

UNION

SELECT 'dai' AS token, HOUR::DATE AS date , AVG(price) AS price FROM ethereum.core.fact_hourly_token_prices WHERE token_address = lower('0x6B175474E89094C44Da98b954EedeAC495271d0F') GROUP BY 1,2

),

O_price AS (

SELECT 'atom' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol ilike '%atom%' AND provider = 'coinmarketcap' GROUP BY 1 ,2

UNION

SELECT 'luna' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol ilike '%luna%' AND provider = 'coinmarketcap' GROUP BY 1 ,2

UNION

SELECT 'osmo' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol ilike '%osmo%' AND provider = 'coinmarketcap' GROUP BY 1 ,2

SELECT 'usd' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol ilike '%USTC%' AND provider = 'coingecko' GROUP BY 1 ,2

UNION

SELECT 'aevmos' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol ilike '%EVMOS%' AND provider = 'coinmarketcap' GROUP BY 1 ,2

UNION

SELECT 'mntl' AS token , RECORDED_AT::DATE AS date , AVG(price) AS price FROM osmosis.core.dim_prices WHERE symbol like '%MNTL%' AND provider = 'coingecko' GROUP BY 1 ,2

),

minetab_price AS(