select

tbals.hour,

contract_address,

tbals.symbol,

sum(balance_last * price) as balance_usd

(

select

date_trunc('hour', block_timestamp) as hour,

user_address,

contract_address,

symbol,

min(balance / pow(10, cons.decimals)) as balance_last

from

ethereum.core.fact_token_balances bals

join ethereum.core.dim_labels labs on labs.address = bals.user_address

join ethereum.core.dim_contracts cons on cons.address = bals.contract_address

where

bals.block_timestamp > current_date - 5

and lower(labs.label) = 'binance'

and labs.label_subtype <> 'deposit_wallet'

group by

1,

2,

3,

4

) tbals

join ethereum.core.fact_hourly_token_prices prices on tbals.contract_address = prices.token_address

and tbals.hour = prices.hour

group by

1,

2,

3

order by

1 desc