SELECT

DATE_TRUNC("DAY", HOUR) AS BASE_DATE,

AVG(PRICE) AS BTC_PRICE

FROM bitcoin.price.ez_prices_hourly

GROUP BY BASE_DATE

ORDER BY BASE_DATE DESC

LIMIT 400

),

LUNA_TABLE AS (

SELECT

DATE_TRUNC("DAY", HOUR) AS COIN_DATE,

AVG(PRICE / POWER(10, COALESCE(DECIMALS, 0))) AS LUNA_PRICE

FROM crosschain.price.ez_prices_hourly

WHERE TOKEN_ADDRESS = 'ibc/785AFEC6B3741100D15E7AF01374E3C4C36F24888E96479B1C33F5C71F364EF9'

GROUP BY COIN_DATE

ORDER BY COIN_DATE DESC

LIMIT 400

SELECT

(LUNA_PRICE / BTC_PRICE) AS PRICE, COIN_DATE

FROM BTC_TABLE

LEFT JOIN LUNA_TABLE ON BASE_DATE = COIN_DATE

ORDER BY COIN_DATE DESC;