with sol_prise as (
select
date_trunc('day', hour) as date,
avg(price) as sol_usd
from ethereum.core.fact_hourly_token_prices
where
token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c'
group by 1
),
base as (
select *
from solana.core.fact_nft_sales s join sol_prise p on s.block_timestamp::date = p.date
where
block_timestamp::date >= '2022-09-22' and
marketplace = 'hyperspace' and
succeeded = true
)
select
contract_name as nft_collection,
sum(sales_amount) * avg(sol_usd) as sales_volume
from base b left join solana.core.dim_nft_metadata m on b.mint = m.contract_address
where nft_collection is not null
group by 1
order by 2 desc
limit 10