WITH price AS(
SELECT hour::date AS price_day, AVG(price) AS price
FROM ethereum.core.fact_hourly_token_prices
WHERE TOKEN_ADDRESS = '0xd31a59c85ae9d8edefec411d448f90841571b89c'
GROUP BY 1
ORDER BY 1
)
SELECT TO_CHAR(DATE_TRUNC(HOUR, block_timestamp), 'YYYY-MM-DD:hh') AS hour, marketplace,
AVG(sales_amount) AS average_sales_amount,
AVG(sales_amount*price) AS average_sales_amount_usd
FROM solana.core.fact_nft_sales
INNER JOIN price ON price_day = block_timestamp::DATE
WHERE marketplace = 'coral cube'
GROUP BY 1,2
ORDER BY 1