poly_price as (

SELECT

trunc(hour,'day') as day,

avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where token_address=lower('0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0') group by 1

),

poly as (

SELECT

trunc(block_timestamp,'{{granularity}}') as date,

count(distinct tx_hash) as txs,

sum(txs) over (order by date) as cum_txs,

avg(tx_fee*avg_price) as avg_tx_fee,

sum(tx_fee*avg_price) as fees_usd,

sum(fees_usd) over (order by date) as cum_fees_usd,

count(distinct from_address) as active_users,

sum(active_users) over (order by date) as cum_active_users

from polygon.core.fact_transactions x

join poly_price y on trunc(block_timestamp,'day')=y.day

where block_timestamp>=CURRENT_DATE- INTERVAL '{{period}}'

group by 1

),

sol_price as (

SELECT

trunc(recorded_hour,'day') as day,

avg(close) as avg_price

from solana.core.fact_token_prices_hourly

where symbol='SOL' group by 1

),

sol as (

SELECT

trunc(block_timestamp,'{{granularity}}') as date,

count(distinct tx_id) as txs,

sum(txs) over (order by date) as cum_txs,

avg(fee*avg_price)/pow(10,6) as avg_tx_fee,