a.BLOCK_ID,

a.BLOCK_TIMESTAMP,

count(distinct a.tx_id) as tx_count

from solana.core.fact_transactions a

join solana.core.fact_blocks b on b.BLOCK_ID = a.BLOCK_ID

where a.block_timestamp >= '2023-08-15'

and a.block_timestamp::date < current_date

group by 1,2)

select

'Solana' as "Network",

count(distinct BLOCK_ID) as "Total Produced Blocks",

((sum(tx_count))/(count(distinct block_id))) as "Average Txs per Block"

from base)

union all

(select

'Sei' as "Network",

count(distinct BLOCK_ID) as "Total Produced Blocks",

((sum(tx_count))/(count(distinct block_id))) as "Average Txs per Block"

from sei.core.fact_blocks

where block_timestamp >= '2023-08-15'

and block_timestamp::date < current_date)

union all

(select

'Osmosis' as "Network",

count(distinct BLOCK_ID) as "Total Produced Blocks",

((sum(tx_count))/(count(distinct block_id))) as "Average Txs per Block"

from Osmosis.core.fact_blocks

where block_timestamp >= '2023-08-15'

and block_timestamp::date < current_date)