with matic_price as (

select date_trunc(day, hour) as date_price,

avg(price) as matic_price,

symbol

from ethereum.core.fact_hourly_token_prices

where token_address = lower('0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0')

group by date_price, symbol

select date_trunc(day, block_timestamp) as date,

count(*) as tx_count,

count(distinct from_address) as unique_address,

matic_price,

sum(tx_count) over (order by date) as cumu_tx,

sum(unique_address) over (order by date) as cumu_address,

tx_count/unique_address as tx_addr_ratio,

cumu_tx/cumu_address as cumu_tx_addr_ratio

from polygon.core.fact_transactions join matic_price on block_timestamp::date = date_price

where

-- where block_timestamp::date >= '2022-07-01'

status = 'SUCCESS'

and block_timestamp::date < CURRENT_DATE

group by date, matic_price