select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions

where to_address=lower('0xb5a27c33ba2adecee8cdbe94cef5576e2f364a8f') --bero

UNION ALL

select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions

where to_address=lower('0x2B4141f98B8cD2a03F58bD722D4E8916d2106504') --hibero

UNION ALL

select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions

where to_address=lower('0x580ABF764405aA82dC96788b356435474c5956A7') --voter

UNION ALL

select date_trunc('day',block_timestamp) as day,from_address as user from berachain.testnet.fact_transactions -- plugins deposit/withdrawals

where to_address IN (

select concat('0x',substr(TOPICS[1], 27,64)) as plugin from berachain.testnet.fact_event_logs

where (ORIGIN_TO_ADDRESS=lower('0x580ABF764405aA82dC96788b356435474c5956A7') or ORIGIN_TO_ADDRESS=lower('0x2363BB86cD2ABF89cc059A654f89f11bCceffcA9')

or ORIGIN_TO_ADDRESS=lower('0x1f9505Ae18755915DcD2a95f38c7560Cab149d9C'))

and ORIGIN_FUNCTION_SIGNATURE='0xd8867fc8'

and topics[0]='0xb920b936f556d443772609e0cc06a72c3bb67fc606de10dcdd51323d1ddbc7fc'

)

UNION ALL

select date_trunc('day',block_timestamp) as day,from_address as user from berachain.testnet.fact_transactions -- farms rewards claim

where to_address IN (

select concat('0x',substr(TOPICS[2], 27,64)) as gauge from berachain.testnet.fact_event_logs

where (ORIGIN_TO_ADDRESS=lower('0x580ABF764405aA82dC96788b356435474c5956A7') or ORIGIN_TO_ADDRESS=lower('0x2363BB86cD2ABF89cc059A654f89f11bCceffcA9')

or ORIGIN_TO_ADDRESS=lower('0x1f9505Ae18755915DcD2a95f38c7560Cab149d9C'))

and ORIGIN_FUNCTION_SIGNATURE='0xd8867fc8'

and topics[0]='0xb920b936f556d443772609e0cc06a72c3bb67fc606de10dcdd51323d1ddbc7fc'

)

and ORIGIN_FUNCTION_SIGNATURE='0xc00007b0'

UNION ALL

select date_trunc('day',block_timestamp) as day, from_address as user from berachain.testnet.fact_transactions --staking rewarder

where to_address=lower('0xD6c2BE22e7b766c810690B22234044407dDa1C1B')

),

new_wallets AS (

select first_transaction as day, count(user) as new_users from (

SELECT

user,