with nft_mins as (

select

instruction :accounts[6] ::string as mint

from solana.core.fact_events

where block_timestamp > '2022-11-04'

and program_id = 'Guard1JwRhJkVH6XZhzoYxeBVQe872VH6QggF4BWmS9g' -- Candy Guard Program ID

and instruction :accounts[0] = 'DXrMes8iT45Nga2G66f7Yyxy2iRd9TxoC8sPt5g1p98p' -- Candy Guard

and instruction :accounts[1] = 'CndyV3LdqHUfDLmE5naZjVN8rBZz4tqhdefbAnjHG3JR'

and instruction :accounts[2] = '6A9aRFG7KirCpYJFFcNRetKsFLTxC221zaWZd8rYCLqe' -- Candy Machine

and instruction :accounts[3] = 'A4FM6h8T5Fmh9z2g3fKUrKfZn6BNFEgByR8QGpdbQhk1' -- Candy Machine Authority

and instruction :accounts[9] = 'wuFBfSJFb6TvSk8rZBhTxjp2BbMdwqYP2LhB8eVaxJP' -- Collection Authority

and instruction :accounts[10] = '4mKSoDDqApmF1DqXvVTSL6tu2zixrSSNjqMxUnwvVzy2' -- Collection Mint

), first_sale as (

SELECT min(block_timestamp) as fist_sale

FROM solana.core.fact_nft_sales

WHERE mint in (SELECT * from nft_mins)

SELECT

mint,

median(SALES_AMOUNT * price) as Median_price

FROM solana.core.fact_nft_sales

LEFT outer JOIN solana.core.dim_labels

on mint = address

cross JOIN first_sale

LEFT outer JOIN (

SELECT

date_trunc('day', recorded_hour) as day1,

avg(close) as price

FROM solana.core.fact_token_prices_hourly

WHERE symbol LIKE 'SOL'

GROUP BY 1

) on date_trunc('day', block_timestamp) = day1