-- For each blockchain, get total and labelled addresses in one go

SELECT

'Arbitrum' AS blockchain,

COUNT(DISTINCT address) AS total_addresses,

COUNT(DISTINCT CASE WHEN address IN (SELECT DISTINCT address FROM arbitrum.core.dim_labels) THEN address END) AS labelled_addresses

FROM (

SELECT from_address AS address FROM arbitrum.core.fact_transactions

UNION

SELECT to_address AS address FROM arbitrum.core.fact_transactions

) AS ArbitrumAddresses

UNION ALL

SELECT

'Ethereum' AS blockchain,

COUNT(DISTINCT address),

COUNT(DISTINCT CASE WHEN address IN (SELECT DISTINCT address FROM ethereum.core.dim_labels) THEN address END)

FROM (

SELECT from_address AS address FROM ethereum.core.fact_transactions

UNION

SELECT to_address AS address FROM ethereum.core.fact_transactions

) AS EthereumAddresses

UNION ALL

SELECT

'Optimism' AS blockchain,

COUNT(DISTINCT address),

COUNT(DISTINCT CASE WHEN address IN (SELECT DISTINCT address FROM optimism.core.dim_labels) THEN address END)

FROM (

SELECT from_address AS address FROM optimism.core.fact_transactions

UNION

SELECT to_address AS address FROM optimism.core.fact_transactions

) AS OptimismAddresses