select label,

sum(tx_cnt) as total_tx_cnt

from

(select date(BLOCK_TIMESTAMP) as datetime,

'Aptos' as label,

count(distinct TX_HASH) as tx_cnt

from aptos.core.fact_transactions

where date(BLOCK_TIMESTAMP) >= current_date - interval '30 day'

group by 1,2

union all

select date(BLOCK_TIMESTAMP) as datetime,

'Avalanche' as label,

count(distinct TX_HASH) as tx_cnt

from avalanche.core.fact_transactions

where date(BLOCK_TIMESTAMP) >= current_date - interval '30 day'

group by 1,2

union all

select date(BLOCK_TIMESTAMP) as datetime,

'BNB' as label,

count(distinct TX_HASH) as tx_cnt

from bsc.core.fact_transactions

where date(BLOCK_TIMESTAMP) >= current_date - interval '30 day'

group by 1,2

union all

select date(BLOCK_TIMESTAMP) as datetime,

'Cosmos' as label,

count(distinct TX_ID) as tx_cnt

from cosmos.core.fact_transactions

where date(BLOCK_TIMESTAMP) >= current_date - interval '30 day'

group by 1,2

union all

select date(BLOCK_TIMESTAMP) as datetime,

'Ethereum' as label,

count(distinct TX_HASH) as tx_cnt

from ethereum.core.fact_transactions