select distinct

token_name,

to_number(trim(token_name,'geometry runners #')) AS Number,

project_name,

round(replace(token_metadata:traits[1]:value::string,'Speed: ',''),4) AS Speed,

replace(token_metadata:traits[2]:value::string,'Arrangement: ','') AS Arrangement,

replace(token_metadata:traits[3]:value::string,'Scale Type: ','') AS ScaleType,

replace(token_metadata:traits[4]:value::string,'Creature: ','') AS Creature,

replace(token_metadata:traits[5]:value::string,'Palette: ','') AS Palette,

replace(token_metadata:traits[6]:value::string,'Color Type: ','') AS ColorType,

replace(token_metadata:traits[7]:value::string,'Bg Style: ','') AS BgStyle,

replace(token_metadata:traits[8]:value::string,'Bg Material: ','') AS BgMaterial,

replace(token_metadata:traits[9]:value::string,'Wire: ','') AS Wire

from ethereum.nft_metadata

where contract_address = '0xa7d8d9ef8d8ce8992df33d8b8cf4aebabd5bd270'

and lower(token_name) LIKE '%runner%'

and number <> 0

order by 2)

select arrangement, count(number) from grunners

group by 1

order by 2 desc