token_metadata:collection_name::string as "Collection",

avg(price) as "Average (Ξ)",

median(price) as "Median (Ξ)",

max(price) as "Maximum (Ξ)"

from

ethereum.nft_events

left join ethereum.nft_metadata on ethereum.nft_events.token_id = ethereum.nft_metadata.token_id and ethereum.nft_events.contract_address = ethereum.nft_metadata.contract_address

where ethereum.nft_events.contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a','0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')

and event_type = 'sale'

and token_metadata:collection_name is not null

and tx_currency in ('ETH','WETH')

and price is not null

and token_metadata:collection_name::string in

(select

token_metadata:collection_name

from

ethereum.nft_events

left join ethereum.nft_metadata on ethereum.nft_events.token_id = ethereum.nft_metadata.token_id and ethereum.nft_events.contract_address = ethereum.nft_metadata.contract_address

where ethereum.nft_events.contract_address in ('0x059edd72cd353df5106d2b9cc5ab83a52287ac3a','0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270')

and event_type = 'sale'

and token_metadata:collection_name is not null

group by 1

order by sum(price) desc

limit 10)

group by "Collection"

order by "Collection"