select

TO_ADDRESS as receiver,

sum(RAW_AMOUNT/pow(10, 8)) as BTC_received

from avalanche.core.fact_token_transfers

where 1=1

and CONTRACT_ADDRESS = '0x152b9d0fdc40c096757f570a51e494bd4b943e50'

and from_address = '0x0000000000000000000000000000000000000000'

group by 1

),

send as (

select

FROM_ADDRESS as sender,

sum(RAW_AMOUNT/pow(10, 8)) as BTC_sent

from avalanche.core.fact_token_transfers

where 1=1

and CONTRACT_ADDRESS = '0x152b9d0fdc40c096757f570a51e494bd4b943e50'

and to_address = '0x0000000000000000000000000000000000000000'

group by 1

),

net_balance as (

select

receiver as Holder,

BTC_received - BTC_sent as Balance

from receive a join send b on a.receiver = b.sender

select

CASE

when Balance > 0 and Balance < 0.1 then 'Tier 1: Less than 0.1 BTC'

when Balance >= 0.1 and Balance < 1 then 'Tier 2: 0.1-1 BTC'

when Balance >= 1 and Balance < 10 then 'Tier 3: 1-10 BTC'

when Balance >= 10 and Balance < 100 then 'Tier 4: 10-100 BTC'

when Balance >= 100 and Balance < 500 then 'Tier 5: 100-500 BTC'

else 'Tier 6: More than 500 BTC'

end as status,

count(distinct Holder) as "# Holders",