SELECT label,

avg(n_txns),

avg(tps),

avg(success_rate),

SELECT 'NEAR' as label,

'L1' as sub_label,

date_trunc('hour',block_timestamp) as date,

sum(CASE WHEN tx_status = 'Success' THEN 1 ELSE 0 END) as successful_txns,

sum(CASE WHEN tx_status = 'Fail' THEN 1 ELSE 0 END) as unsuccessful_txns,

successful_txns+unsuccessful_txns as n_txns,

successful_txns/(3600) as tps,

100*successful_txns/(successful_txns+unsuccessful_txns) as success_rate,

COUNT(DISTINCT tx_signer) as n_users,

n_txns / n_users as txs_per_user

FROM near.core.fact_transactions

WHERE date between current_date-30 and CURRENT_DATE-1

GROUP BY date

UNION

SELECT 'Polygon' as label,

'L1' as sub_label,

date_trunc('hour',block_timestamp) as date,

sum(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) as successful_txns,

sum(CASE WHEN status != 'SUCCESS' THEN 1 ELSE 0 END) as unsuccessful_txns,

successful_txns+unsuccessful_txns as n_txns,

successful_txns/(3600) as tps,

100*successful_txns/(successful_txns+unsuccessful_txns) as success_rate,

COUNT(DISTINCT from_address) as n_users,

n_txns / n_users as txs_per_user

FROM polygon.core.fact_transactions

WHERE date between current_date-30 and CURRENT_DATE-1

GROUP BY date