sum(case when transfer_type='out' then value else 0 end ) as total_claim_MON_amount

,count(distinct case when transfer_type='out' then hash else null end ) as total_claim_tx_count

,count(distinct case when transfer_type='out' then other_address else null end ) as total_claim_address_count

,sum(case when transfer_type='out' then value else 0 end )/count(distinct case when transfer_type='out' then other_address else null end ) as avg_claimed_per_address

from

SELECT

FROM_ADDRESS AS core_address,

TO_ADDRESS AS other_address,

'out' AS transfer_type,

value,

BLOCK_TIMESTAMP AS stat_date,

TX_HASH as hash

FROM monad.testnet.fact_transactions

WHERE from_address=lower('0xD7a24d1F1435CD314E86736E139f8431D4498D4e')

and BLOCK_TIMESTAMP>='2025-02-19 14:00:00'

UNION ALL

SELECT

TO_ADDRESS AS core_address,

FROM_ADDRESS AS other_address,

'in' AS transfer_type,

value,

BLOCK_TIMESTAMP AS stat_date,

TX_HASH as hash

FROM monad.testnet.fact_transactions

WHERE to_address=lower('0xD7a24d1F1435CD314E86736E139f8431D4498D4e')

and BLOCK_TIMESTAMP>='2025-02-19 14:00:00'

) a