m.token_name,

s.avg_price_ETH,

s.max_price_ETH,

s.avg_price_USD,

s.count_sales

FROM ethereum.nft.dim_nft_metadata m

INNER JOIN (

SELECT

tokenid,

ROUND(AVG(price), 2) AS avg_price_ETH,

ROUND(MAX(price), 2) AS max_price_ETH,

ROUND(AVG(price_usd), 2) AS avg_price_USD,

COUNT(event_type) AS count_sales

FROM

ethereum.nft.ez_nft_sales

WHERE

currency_symbol = 'ETH' AND

event_type = 'sale'

GROUP BY

tokenid) s ON s.tokenid = m.token_id

WHERE m.project_name LIKE('%bored_ape_yacht_club%')

ORDER BY

avg_price_ETH DESC,

avg_price_USD DESC,

count_sales DESC

LIMIT 10;