(SELECT distinct CONCAT('0x', LTRIM(CONTRACT, '0x')) as addr, case when

CLASS_HASH = '0x03530cc4759d78042f1b543bf797f5f3d647cde0388c33734cf91b7f7b9314a9' then 'Okex'

When CLASS_HASH = '0x058d97f7d76e78f44905cc30cb65b91ea49a4b908a76703c54197bca90f81773' then 'bitstamp'

When CLASS_HASH = '0x025ec026985a3bf9d0cc1fe17326b245dfdc3ff89b8fde106542a3ea56c5a918' then 'argentx'

When CLASS_HASH = '0x03131fa018d520a037686ce3efddeab8f28895662f019ca3ca18a626650f7d1e' then 'braavos' else 'other' end as provider

from external.tokenflow_starknet.decoded_transactions

where

type like '%DEPLOY%' and CHAIN_ID = 'mainnet'

),

outflow as (SELECT PARAMETERS[0]:value::string as sender,-1*sum (PARAMETERS[2]:value[0]:value::string /pow(10,18)) as vol0

from external.tokenflow_starknet.decoded_events

where

CHAIN_ID = 'mainnet'

and name = 'Transfer'

and contract = '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

GROUP by 1),

inflow as (SELECT PARAMETERS[1]:value::string as rec,sum (PARAMETERS[2]:value[0]:value::string /pow(10,18)) as vol1

from external.tokenflow_starknet.decoded_events

where

CHAIN_ID = 'mainnet'

and name = 'Transfer'

and contract = '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

GROUP by 1),

final as

(SELECT DISTINCT sender as user, vol0 as outflow, vol1 as inflow, outflow + inflow as bal , case when bal > 0 then bal else 0 end as balance

from outflow join inflow on sender = rec)

SELECT sum(balance)/ count(DISTINCT user) as avg_ETH_bal, provider

from final join accs on addr = user