SELECT seller_address AS USER_address

, '-1' AS flow

FROM ethereum.core.ez_nft_sales

WHERE nft_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'

UNION ALL

SELECT buyer_address AS USER_address

, '1' AS flow

FROM ethereum.core.ez_nft_sales

WHERE nft_address = '0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb'

),

holdings AS (

SELECT USER_address

, SUM(flow) AS holdings

FROM raw

WHERE user_address != '0x0000000000000000000000000000000000000000'

GROUP BY 1

having holdings > 0

),

ranked AS (

SELECT USER_address

, holdings

, row_number() OVER (ORDER BY holdings DESC) AS rank

FROM holdings

),

top_holders AS (

SELECT USER_address

, rank

FROM ranked

WHERE rank <= 10

),

holders_spent AS (

SELECT buyer_address AS address

, SUM(nft.price) AS eth_spent