select * from ethereum.core.dim_labels

where label ilike '%binance%' or address_name ilike '%binance%'

),

inflow as (

select

date_trunc('Hour', block_timestamp) as hours,

count(DISTINCT(from_address)) as users,

sum(amount_usd) as volume,

count(DISTINCT(tx_hash)) as transactions

from ethereum.core.ez_token_transfers

where hours >= '2022-11-05'

and to_address in (select DISTINCT(address) from binance)

and from_address not in (select DISTINCT(address) from binance)

group by 1

),

outflow as (

select

date_trunc('Hour', block_timestamp) as hours,

count(DISTINCT(to_address)) as users,

count(DISTINCT(tx_hash)) as transactions,

sum(amount_usd) as volume

from ethereum.core.ez_token_transfers

where hours >= '2022-11-05'

and from_address in (select DISTINCT(address) from binance)

and to_address not in (select DISTINCT(address) from binance)

group by 1

select inf.hours as hours, inf.transactions as inflow_transactions, inf.users as inflow_users, inf.volume as inflow_amount,

outf.transactions as outflow_transactions, outf.users as outflow_users, (outf.volume*-1) as outflow_amount,

inf.volume - outf.volume as net_volume

from inflow inf join outflow outf using(hours)