select

project_name,

nft_address,

buyer_address,

count(*) bought,

avg(price_USD) avgPriceUSD,

sum(price_USD) totalUSD

from ethereum.core.ez_nft_sales

where block_timestamp::date >= GETDATE()::DATE - 7

group by project_name, nft_address, buyer_address

),

agg as (

select *,

RANK() OVER(PARTITION BY project_name, nft_address ORDER BY bought desc) as rank

from buyers

select *

from agg

where 1=1

and rank <= 3

and bought > 10

and avgPriceUSD > 100

order by bought desc