map(

'Ethereum', map(

unhex('0316EB71485b0Ab14103307bf65a021042c6d380'), ('0x0316EB71485b0Ab14103307bf65a021042c6d380', 'HBTC', 18, 'Derivative', 'Ethereum'),

unhex('eb4c2781e4eba804ce9a9803c67d0893436bb27d'), ('0xeb4c2781e4eba804ce9a9803c67d0893436bb27d', 'renBTC', 8, 'Derivative', 'Ethereum'),

unhex('fe18be6b3bd88a2d2a7f928d00292e7a9963cfc6'), ('0xfe18be6b3bd88a2d2a7f928d00292e7a9963cfc6', 'sBTC', 18, 'Derivative', 'Ethereum'),

unhex('18084fba666a33d37592fa2633fd49a74dd93a88'), ('0x18084fba666a33d37592fa2633fd49a74dd93a88', 'tBTC', 18, 'Derivative', 'Ethereum'),

unhex('3212b29E33587A00FB1C83346f5dBFA69A458923'), ('0x3212b29E33587A00FB1C83346f5dBFA69A458923', 'imBTC', 8, 'Derivative', 'Ethereum'),

unhex('0327112423f3a68efdf1fcf402f6c5cb9f7c33fd'), ('0x0327112423f3a68efdf1fcf402f6c5cb9f7c33fd', 'BTC++', 18, 'Derivative', 'Ethereum'),

unhex('C96dE26018A54D51c097160568752c4E3BD6C364'), ('0xC96dE26018A54D51c097160568752c4E3BD6C364', 'FBTC', 8, 'Derivative', 'Ethereum'),

unhex('9BE89D2a4cd102D8Fecc6BF9dA793be995C22541'), ('0x9BE89D2a4cd102D8Fecc6BF9dA793be995C22541', 'BBTC (Binance)', 8, 'Derivative', 'Ethereum'),

unhex('F5e11df1ebCf78b6b6D26E04FF19cD786a1e81dC'), ('0xF5e11df1ebCf78b6b6D26E04FF19cD786a1e81dC', 'BBTC (BounceBit)', 18, 'Derivative', 'Ethereum'),

unhex('7A56E1C57C7475CCf742a1832B028F0456652F97'), ('0x7A56E1C57C7475CCf742a1832B028F0456652F97', 'SolvBTC', 18, 'Derivative', 'Ethereum'),

unhex('cbb7c0000ab88b473b1f5afd9ef808440eed33bf'), ('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'cbBTC', 8, 'Derivative', 'Ethereum'),

unhex('8236a87084f8B84306f72007F36F2618A5634494'), ('0x8236a87084f8B84306f72007F36F2618A5634494', 'LBTC', 8, 'Staked', 'Ethereum'),

unhex('bdf245957992bfbc62b07e344128a1eec7b7ee3f'), ('0xbdf245957992bfbc62b07e344128a1eec7b7ee3f', 'mBTC', 8, 'Staked', 'Ethereum'),

unhex('004E9C3EF86bc1ca1f0bB5C7662861Ee93350568'), ('0x004E9C3EF86bc1ca1f0bB5C7662861Ee93350568', 'uniBTC', 8, 'Staked', 'Ethereum'),

unhex('f6718b2701D4a6498eF77D7c152b2137Ab28b8A3'), ('0xf6718b2701D4a6498eF77D7c152b2137Ab28b8A3', 'stBTC', 18, 'Staked', 'Ethereum'),

unhex('F469fBD2abcd6B9de8E169d128226C0Fc90a012e'), ('0xF469fBD2abcd6B9de8E169d128226C0Fc90a012e', 'pumpBTC', 8, 'Staked', 'Ethereum'),

unhex('8DB2350D78aBc13f5673A411D4700BCF87864dDE'), ('0x8DB2350D78aBc13f5673A411D4700BCF87864dDE', 'swBTC', 8, 'Restaked', 'Ethereum'),

unhex('d9D920AA40f578ab794426F5C90F6C731D159DEf'), ('0xd9D920AA40f578ab794426F5C90F6C731D159DEf', 'SolvBTC.BBN', 18, 'Restaked', 'Ethereum')

),

'Arbitrum', map(

unhex('6c84a8f1c29108F47a79964b5Fe888D4f4D0dE40'), ('0x6c84a8f1c29108F47a79964b5Fe888D4f4D0dE40', 'tBTC', 18, 'Derivative', 'Arbitrum'),

unhex('3647c54c4c2c65bc7a2d63c0da2809b399dbbdc0'), ('0x3647c54c4c2c65bc7a2d63c0da2809b399dbbdc0', 'SolvBTC', 18, 'Derivative', 'Arbitrum'),

unhex('f6718b2701D4a6498eF77D7c152b2137Ab28b8A3'), ('0xf6718b2701D4a6498eF77D7c152b2137Ab28b8A3', 'stBTC', 18, 'Staked', 'Arbitrum'),

unhex('346c574C56e1A4aAa8dc88Cda8F7EB12b39947aB'), ('0x346c574C56e1A4aAa8dc88Cda8F7EB12b39947aB', 'SolvBTC.BBN', 18, 'Restaked', 'Arbitrum'),

unhex('aFAfd68AFe3fe65d376eEC9Eab1802616cFacCb8'), ('0xaFAfd68AFe3fe65d376eEC9Eab1802616cFacCb8', 'SolvBTC.ENA', 18, 'Restaked', 'Arbitrum')

),

'BNB', map(

unhex('4aae823a6a0b376De6A78e74eCC5b079d38cBCf7'), ('0x4aae823a6a0b376De6A78e74eCC5b079d38cBCf7', 'SolvBTC', 18, 'Derivative', 'BNB'),

unhex('f9C4FF105803A77eCB5DAE300871Ad76c2794fa4'), ('0xf9C4FF105803A77eCB5DAE300871Ad76c2794fa4', 'pumpBTC', 8, 'Staked', 'BNB'),

unhex('f6718b2701D4a6498eF77D7c152b2137Ab28b8A3'), ('0xf6718b2701D4a6498eF77D7c152b2137Ab28b8A3', 'stBTC', 18, 'Staked', 'BNB'),

unhex('1346b618dc92810ec74163e4c27004c921d446a5'), ('0x1346b618dc92810ec74163e4c27004c921d446a5', 'SolvBTC.BBN', 18, 'Restaked', 'BNB'),

unhex('53E63a31fD1077f949204b94F431bCaB98F72BCE'), ('0x53E63a31fD1077f949204b94F431bCaB98F72BCE', 'SolvBTC.ENA', 18, 'Restaked', 'BNB')

),

'Base', map(

unhex('cbb7c0000ab88b473b1f5afd9ef808440eed33bf'), ('0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf', 'cbBTC', 8, 'Derivative', 'Base')

)

) as wrapped_btcs,

-- Calculate total BTC received by each recipient across chains

recipient_flows as (

-- Ethereum

select

input_1_value_address as address,

wrapped_btcs['Ethereum'][e.address].2 as symbol,

wrapped_btcs['Ethereum'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Ethereum'][e.address].3)) as total_received_btc

from evm_events_ethereum_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Ethereum'])

and input_1_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- Arbitrum

select

input_1_value_address as address,

wrapped_btcs['Arbitrum'][e.address].2 as symbol,

wrapped_btcs['Arbitrum'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Arbitrum'][e.address].3)) as total_received_btc

from evm_events_arbitrum_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Arbitrum'])

and input_1_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- BNB

select

input_1_value_address as address,

wrapped_btcs['BNB'][e.address].2 as symbol,

wrapped_btcs['BNB'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['BNB'][e.address].3)) as total_received_btc

from evm_events_bsc_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['BNB'])

and input_1_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- Base

select

input_1_value_address as address,

wrapped_btcs['Base'][e.address].2 as symbol,

wrapped_btcs['Base'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Base'][e.address].3)) as total_received_btc

from evm_events_base_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Base'])

and input_1_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

),

-- Calculate total BTC sent by each sender address across chains

sender_flows as (

-- Ethereum

select

input_0_value_address as address,

wrapped_btcs['Ethereum'][e.address].2 as symbol,

wrapped_btcs['Ethereum'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Ethereum'][e.address].3)) as total_sent_btc

from evm_events_ethereum_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Ethereum'])

and input_0_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- Arbitrum

select

input_0_value_address as address,

wrapped_btcs['Arbitrum'][e.address].2 as symbol,

wrapped_btcs['Arbitrum'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Arbitrum'][e.address].3)) as total_sent_btc

from evm_events_arbitrum_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Arbitrum'])

and input_0_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- BNB

select

input_0_value_address as address,

wrapped_btcs['BNB'][e.address].2 as symbol,

wrapped_btcs['BNB'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['BNB'][e.address].3)) as total_sent_btc

from evm_events_bsc_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['BNB'])

and input_0_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

UNION ALL

-- Base

select

input_0_value_address as address,

wrapped_btcs['Base'][e.address].2 as symbol,

wrapped_btcs['Base'][e.address].5 as chain,

sum(cast(input_2_value_uint256 as double) / power(10, wrapped_btcs['Base'][e.address].3)) as total_sent_btc

from evm_events_base_mainnet_v1 e

where e.signature = 'Transfer(address,address,uint256)'

and e.address in mapKeys(wrapped_btcs['Base'])

and input_0_value_address != '0x0000000000000000000000000000000000000000'

and timestamp >= now() - interval 90 day

group by address, symbol, chain

),

-- Join received and sent flows to get both totals for each address

address_flows as (

select

coalesce(r.address, s.address) as address,

coalesce(r.symbol, s.symbol) as symbol,

coalesce(r.chain, s.chain) as chain,

coalesce(r.total_received_btc, 0) as total_received_btc,

coalesce(s.total_sent_btc, 0) as total_sent_btc

from recipient_flows r

full outer join sender_flows s

on r.address = s.address

and r.symbol = s.symbol

and r.chain = s.chain

)

CASE

WHEN address = '0x5c647ce0ae10658ec44fa4e11a51c96e94efd1dd' THEN 'Aave Ethereum cbBTC (aEthcbBTC)'

WHEN address = '0x8bc93498b861fd98277c3b51d240e7e56e48f23c' THEN 'uniBTC'

WHEN address = '0x657e8c867d8b37dcc18fa4caead9c45eb088c642' THEN 'etherfi eBTC'

WHEN address = '0x468c34703f6c648ccf39dbab11305d17c70ba011' THEN 'Karak LBTC'

WHEN address = '0x5401b8620e5fb570064ca9114fd1e135fd77d57c' THEN 'Lombard BTC Vault (LBTCv)'

WHEN address = '0xbbbbbbbbbb9cc5e90e3b3af64bdaf62c37eeffcb' THEN 'Morpho Blue'

WHEN address = '0x10ac93971cdb1f5c778144084242374473c350da' THEN 'Aave Ethereum tBTC (aEthtBTC)'

WHEN address = '0x141ec2d606f12ff959d7d07cde6811e5fdff2831' THEN 'SY SolvBTC Babylon (SY-SolvBTC)'

WHEN address = '0xc781c0cc527cb8c351be3a64c690216c535c6f36' THEN 'SY Lombard LBTC (SY-LBTC)'

WHEN address = '0xab13b8eecf5aa2460841d75da5d5d861fd5b8a39' THEN 'Threshold BTC (tBTC)'

WHEN address = '0xa4ca04d02bfdc3a2df56b9b6994520e69df43f67' THEN 'Fuel Protocol'

WHEN address = '0x18a5a3d575f34e5eba92ac99b0976dbe26f9f869' THEN 'Lombard upLBTC'

WHEN address = '0x38d43a6cb8da0e855a42fb6b0733a0498531d774' THEN 'Swell Network - Simple Staking'

WHEN address = '0x42a856dbebb97abc1269eab32f3bb40c15102819' THEN 'Sat Layer'

WHEN address = '0x87428a53e14d24ab19c6ca4939b4df93b8996ca9' THEN 'Uniswap V3: LBTC - WBTC'

ELSE address -- Fallback to the actual wallet address if no label is found

END AS address,

symbols,

chains,

total_received_btc,

total_sent_btc,

(total_received_btc - total_sent_btc) as net_btc_balance

from (

select

distinct(address),

groupArray(symbol) as symbols, -- Aggregates symbols into an array

groupArray(chain) as chains, -- Aggregates chains into an array

sum(total_received_btc) as total_received_btc, -- Summed received BTC

sum(total_sent_btc) as total_sent_btc -- Summed sent BTC

from address_flows

group by address

) as aggregated_data

order by net_btc_balance desc

limit 50;