select user_address, last_balance/1e18 as last_balance, max(nonce) as nonce, min(block_timestamp) as first_tx from (

select distinct user_address, last_value(balance) over (partition by user_address order by b.block_timestamp) as last_balance from ethereum.core.fact_eth_balances b

) b

join ethereum.core.fact_transactions t on t.FROM_ADDRESS=b.user_address

group by 1,2

with badge_minters as (

select

distinct minter as minter,

count(badge_id) as minted_badges

from

(

select

decoded_log ['to'] as minter,

decoded_log ['id'] as badge_id

from

berachain.testnet.fact_decoded_event_logs

where

CONTRACT_ADDRESS = lower('0x886D2176D899796cD1AfFA07Eff07B9b2B80f1be')

and decoded_log ['from'] = lower('0x0000000000000000000000000000000000000000')

)

group by 1

select bm.minter, sum(nonce), min(first_tx) from badge_minters bm

join (

select minter, coalesce(max(arb.nonce),0) as nonce, min(block_timestamp) as first_tx from badge_minters bm

join arbitrum.core.fact_transactions arb on arb.from_address=bm.minter

group by 1

UNION ALL

select minter, coalesce(max(avax.nonce),0) as nonce, min(block_timestamp) as first_tx from badge_minters bm

join avalanche.core.fact_transactions avax on avax.from_address=bm.minter

group by 1

UNION ALL

select minter, coalesce(max(poly.nonce),0) as nonce, min(block_timestamp) as first_tx from badge_minters bm