select

ethereum.nft_events.token_id,

round(avg(price_usd)) as price_USD

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 token_metadata:collection_name::string = 'Chromie Squiggle by Snowfro'

and event_type = 'sale'

and price_usd > 0

group by 1

order by 2 desc, 1

),

mints as (

select

ene.token_id,

token_metadata:features as features

from ethereum.nft_events as ene

inner join ethereum.nft_metadata as mt on ene.contract_address = mt.contract_address and ene.token_id = mt.token_id

where ene.block_timestamp is not null

and ene.project_name = 'art_blocks'

and mt.token_metadata:collection_name::string = 'Chromie Squiggle by Snowfro'

and ene.event_type = 'mint'

,

feature as (

select

token_id,

F.Value::string as feature_string,

SPLIT_PART(feature_string, ':', 1) as feature,

SPLIT_PART(feature_string, ':', 2) as feature_value

from mints,

table(Flatten(mints.features)) F

),