WITH rarities AS (

SELECT token_metadata:Earring::string AS value

, COUNT(DISTINCT token_id) AS num_nfts

FROM ethereum.core.dim_nft_metadata

WHERE project_name = 'bored_ape_yacht_club'

GROUP BY 1

), n_sales AS (

SELECT token_metadata:Earring::string AS value

, 'Earring' AS feature

, COUNT(1) AS n_sales

FROM ethereum.core.ez_nft_sales

WHERE

block_timestamp >= CURRENT_DATE - 180

AND project_name = 'boredapeyachtclub'

GROUP BY 1, 2

SELECT n.*

, r.num_nfts

, n.n_sales / r.num_nfts AS sales_ratio

FROM n_sales n

JOIN rarities r ON r.value = n.value