select

'Core' as sym,

Hour::date as pdate,

avg(price) as price

from core.price.ez_prices_Hourly

where symbol = 'CORE'

group by 1,2

'ETH' as sym,

Hour::date as pdate,

avg(price) as price

from ethereum.price.ez_prices_Hourly

where symbol = 'ETH'

and token_address is null

group by 1,2

select

'Arbitrum' as Chain,

date_trunc('{{Frame}}', block_timestamp) as day,

count(distinct TX_HASH) as txs,

txs/count(DISTINCT date_trunc('{{Frame}}', block_timestamp))/(case when '{{Frame}}' = 'Hour' then 3600 when '{{Frame}}' = 'Day' then 86400 when '{{Frame}}' = 'Week' then 604800 when '{{Frame}}' = 'Month' then 2592000 else 1 end) as avg_tps,

COUNT(DISTINCT tx_hash) / TIMESTAMPDIFF(SECOND, MIN(block_timestamp), MAX(block_timestamp)) AS avg_tps2,

sum(TX_FEE*price) as Fee,

avg(TX_FEE*price) as avg_Fee,

count(DISTINCT from_Address) as users,

(sum(case when TX_SUCCEEDED ='TRUE' then 1 else 0 end)/txs)*100 as success_rate,

txs/users as tx_per_user,

txs/Count(DISTINCT date_trunc('{{Frame}}', block_timestamp)) as txs_per_day,

users/count(DISTINCT date_trunc('{{Frame}}', block_timestamp)) as users_per_day

from arbitrum.core.fact_transactions