select

hour::date as days,

round(avg(price), 2) as sol_price

from ethereum.core.fact_hourly_token_prices

where token_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c'

group by days

),

volume as (

select

date_trunc('day', block_timestamp) as date,

mint,

sum(SALES_AMOUNT * sol_price) as volume_in_usd

from solana.core.fact_nft_sales

join sol_price_usd on block_timestamp::date = sol_price_usd.days

where marketplace ilike '%magic eden%' and SUCCEEDED = 'TRUE'

and date >='2022-01-01'

group by 1,2

),

top_10 as (

select

project_name,

sum(volume_in_usd) as amount

from volume a left join solana.core.dim_nft_metadata b on a.mint=b.mint

where date>= '2022-01-01'

group by 1

order by 2 desc

limit 10

select * from top_10 limit 10