WITH avalanche_Price AS

SELECT

date_trunc('day', HOUR) as timestamps,

AVG(PRICE) as avax_Price

FROM

ethereum.core.fact_hourly_token_prices

WHERE

SYMBOL = 'WAVAX' AND timestamps >= '2023-01-01'

GROUP BY

1

),

transactions_t AS

SELECT

BLOCK_TIMESTAMP,

TX_HASH,

FROM_ADDRESS,

TX_FEE

FROM

avalanche.core.fact_transactions

WHERE

STATUS = 'SUCCESS'

SELECT

date_trunc('day', BLOCK_TIMESTAMP) as timestamps,

COUNT(distinct TX_HASH) as daily_Transaction,

COUNT(distinct FROM_ADDRESS) as daily_User,

SUM(TX_FEE) / daily_Transaction as daily_Fee,

AVG(avax_Price) as daily_Avax_Price

FROM

transactions_t a JOIN avalanche_Price b ON a.BLOCK_TIMESTAMP::date = b.timestamps