select f.address, claims, total_claimed, latest_claim, max(nonce) as txs from (

select address, count(distinct tx_hash) as claims, sum(amount) as total_claimed, max_by(amount, block_timestamp) as latest_claim

from (

select block_timestamp, tx_hash, concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount from berachain.testnet.fact_event_logs

where topics[0]='0x7cad7fbe1215c486c724bf41124e0ed689d280724381379da844556025c463c1'

and contract_address=lower('0xF1E4A550772faBfc35B28b51eB8d0b6FCd1c4878')

and block_timestamp <= '2024-09-25 16:20:00'

)

group by 1 having (count(distinct tx_hash)>=5) --claimed atleast 5 times

) f

join berachain.testnet.fact_transactions t on lower(t.FROM_ADDRESS)=lower(f.address)

where t.block_timestamp <= '2024-09-25 16:20:00'

group by 1,2,3,4 having (max(nonce)>=5)

),

mainnet_activity as (

select user_address, max_by(balance/1e18, block_timestamp) as eth_balance from ethereum.core.fact_eth_balances

group by user_address having max_by(balance/1e18, block_timestamp)>0.001 -- mainnet balance req to claim from faucet on CQ site

select address, claims, total_claimed, latest_claim, txs as testnet_txs, eth_balance from faucet_claimers f

join mainnet_activity m on lower(m.user_address)=lower(f.address)

where f.address not in (lower('0xbb3e89b5c67cd64ff7907eb6f2bfdb5a788fc427'),lower('0x854ecc1d38be0870d44b4c9415cfbb21cbd9d779'))