main as (

select

tx_id as tx_hash,

block_timestamp,

authorizers[0]::string as user,

'Flow' as chain

from

flow.core.fact_transactions

where

block_timestamp::date >= current_date - interval '30 days'

and tx_succeeded

union all

select

tx_hash,

block_timestamp,

from_address as user,

'Flow EVM' as chain

from

flow.core_evm.fact_transactions

where

block_timestamp::date >= current_date - interval '30 days'

and tx_succeeded

)

select

chain,

count(distinct tx_hash) as transactions,

count(distinct user) as users,

transactions / datediff('seconds', min(block_timestamp), max(block_timestamp)) as tps,

transactions / count(distinct block_timestamp::date) as daily_average_transactions,

users / count(distinct block_timestamp::date) as daily_average_users

from