select block_timestamp as bought_at,

tx_hash as buy_tx,

nft_to_address as holder,

from ethereum.nft.ez_nft_transfers

where nft_address=lower('0xbd3531da5cf5857e7cfaa92426877b022e612cf8')

and block_timestamp < '2024-12-17 12:52:23.000'

qualify(row_number() over (partition by tokenId order by block_timestamp desc, event_index desc) =1)

),

temp as

select buyer_address, count(distinct tx_hash) as nft_trades, sum(price_usd) as total_volume,

count(case when nft_address='0xbd3531da5cf5857e7cfaa92426877b022e612cf8' then 1 else null end) as pudgy_trades,

sum(case when nft_address='0xbd3531da5cf5857e7cfaa92426877b022e612cf8' then price_usd else null end) as pudgy_volume

from ethereum.nft.ez_nft_sales

where 1=1

and buyer_address in ( select holder from holders_at_snapshot)

group by buyer_address

union

select seller_address, count(distinct tx_hash) as nft_trades, sum(price_usd) as total_volume ,

count(case when nft_address='0xbd3531da5cf5857e7cfaa92426877b022e612cf8' then 1 else null end) as pudgy_trades,

sum(case when nft_address='0xbd3531da5cf5857e7cfaa92426877b022e612cf8' then price_usd else null end) as pudgy_volume

from ethereum.nft.ez_nft_sales

where 1=1

and seller_address in ( select holder from holders_at_snapshot)

group by seller_address

select buyer_address as address, sum(nft_trades) as nft_trades, sum (total_volume) as total_volume, sum(pudgy_trades) as pudgy_trades,

coalesce(sum(pudgy_volume),0) as pudgy_volume

from temp

group by address