select s.*, m.project_name

from solana.core.fact_nft_mints s

inner join solana.core.dim_nft_metadata m on m.mint=s.mint),

first_nft_mint as

(

select * from nft_mint_data

qualify (row_number() over(partition by project_name order by block_timestamp asc)=1)

),

new_projects as (

select project_name from first_nft_mint

where block_timestamp > '2022-07-01'

),

price as (

select hour::date as "date", avg(price) as sol_price

from ethereum.core.fact_hourly_token_prices

where token_address=lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')

group by "date"

),

nft_sale_data as

select s.*, m.project_name,s.sales_amount*p.sol_price as sale_in_usd

from solana.core.fact_nft_sales s

inner join solana.core.dim_nft_metadata m on m.mint=s.mint

inner join price p on s.block_timestamp::date=p."date")

select project_name, sum(sale_in_usd) as sum from nft_sale_data

where block_timestamp::date >= {{start_date}}

and project_name is not null

and project_name in (

select project_name from new_projects

)

group by 1