WITH t0 AS (

SELECT DISTINCT mint

, contract_name

FROM solana.core.dim_nft_metadata

WHERE contract_name IN (

'DeGods',

'Solana Monkey Business',

'Taiyo Robotics',

-- 'Blocksmith Labs',

'Degen Apes',

-- 'Okay Bears',

'Boryoku Dragonz',

'Famous Fox Federation',

'Shadowy Super Coder',

'Aurory'

)

), t1 AS (

SELECT mint

, AVG(sales_amount) AS avg

FROM solana.core.fact_nft_sales s

WHERE block_timestamp >= '2022-01-01'

-- WHERE block_timestamp >= CURRENT_DATE - 180

GROUP BY 1

), t2 AS (

SELECT contract_name AS collection

, COUNT(DISTINCT t0.mint) AS n_mints

, COUNT(DISTINCT t1.mint) AS n_mints_sold

, AVG(t1.avg) AS avg

FROM t0

LEFT JOIN t1 ON t1.mint = t0.mint

GROUP BY 1

), t3 AS (

SELECT *

, 100 * (1 - (n_mints_sold / n_mints)) AS pct_held