WITH apt_price AS(

SELECT hour::date AS date,

AVG(price) AS price

FROM aptos.price.ez_hourly_token_prices

WHERE token_address = '0x1::aptos_coin::aptoscoin'

AND hour::date >= '2024-01-01'

GROUP BY 1

),

near_price AS(

SELECT

timestamp::date AS date, AVG(price_usd) AS price

FROM near.price.fact_prices

WHERE symbol ilike 'wNEAR'

AND timestamp::date >= '2024-01-01'

GROUP BY 1

),

final AS (

SELECT

block_timestamp::date AS day,

COUNT(tx_hash) AS transactions,

COUNT(DISTINCT tx_signer) AS signers,

SUM(transaction_fee*price/pow(10,24)) AS fee_paid_usd,

AVG(transaction_fee*price/pow(10,24)) AS avg_fee,

'Near' AS chain_name

FROM near.core.fact_transactions t

INNER JOIN near_price p ON(date = day)

WHERE block_timestamp::date >= '2024-01-01'

AND tx_succeeded

GROUP BY 1