SELECT

from_address,

sum(raw_amount / power(10, decimals)) as USDC_out

FROM ethereum.core.ez_token_transfers

WHERE contract_address LIKE lower('0x6B175474E89094C44Da98b954EedeAC495271d0F')

GROUP BY 1

), tab2 as (

SELECT

to_address,

sum(raw_amount / power(10, decimals)) as USDC_In

FROM ethereum.core.ez_token_transfers

WHERE contract_address LIKE lower('0x6B175474E89094C44Da98b954EedeAC495271d0F')

GROUP BY 1

SELECT

to_address as wallet,

CASE WHEN NOT ADDRESS_NAME is NULL THEN ADDRESS_NAME ELSE 'Other' END as address_name,

CASE WHEN label_subtype is NULL THEN 'wallet' ELSE label_type END as address_type,

USDC_BALANCE

FROM (

SELECT

*,

USDC_IN - CASE WHEN USDC_OUT is NULL THEN 0 ELSE USDC_OUT END as USDC_Balance

from tab2

left outer JOIN tab1

on to_address = from_address

ORDER BY 5 DESC

LIMIT 20

)

LEFT outer JOIN ethereum.core.dim_labels

ON to_address = address

ORDER BY 4 DESC