WITH tab0 as (
SELECT date_trunc('day', hour) as day1, avg(price) as sol_price
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address LIKE lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')
GROUP BY 1
)
SELECT contract_name, sum(sales_amount * sol_price) as volume_usd,
count(DISTINCT tx_id) as sales_events, COUNT(DISTINCT PURCHASER) as buyers
FROM solana.core.fact_nft_sales LEFT OUTER JOIN solana.core.dim_nft_metadata
ON solana.core.fact_nft_sales.mint = solana.core.dim_nft_metadata.mint
LEFT outer JOIN tab0 ON day1 = date_trunc('day', block_timestamp)
WHERE NOT contract_name is NULL
GROUP BY 1
order by 2 DESC limit 10