buyer_address as address,

sum(price_usd) as buy_usd,

count(tx_hash) as buy_txs

from ethereum.core.ez_nft_sales

where nft_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'

and price > 0

and price_usd > 0

group by address

),

sellers as (select

seller_address as address,

sum(price_usd) as sell_usd,

count(tx_hash) as sell_txs

from ethereum.core.ez_nft_sales

where nft_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'

and price > 0

and price_usd > 0

group by address

),

combined as (select

b.address,

ifnull(b.buy_usd,0) + ifnull(s.sell_usd,0) as trading_volume_usd,

b.buy_txs,

s.sell_txs,

b.buy_txs + s.sell_txs as n_trades,

rank() over (order by trading_volume_usd desc) as rank_volume

from buyers b, sellers s