-- forked from fe97d1ae-934c-4dd8-8c41-ef88536f814b
WITH price_table AS (SELECT
date_trunc('day', hour) AS DATE,
avg(price) AS price
FROM ethereum.core.fact_hourly_token_prices
WHERE token_address ilike '0xD31a59c85aE9D8edEFeC411D448f90841571b89c'
GROUP BY 1)
SELECT
PURCHASER,
sum(SALES_AMOUNT*price) AS tot_volume_usd
FROM solana.core.fact_nft_sales x JOIN price_table ON date_trunc('day', BLOCK_TIMESTAMP) = price_table.DATE
JOIN solana.dim_nft_metadata y on x.mint = y.mint
WHERE (/*MARKETPLACE = 'solanart' OR*/ MARKETPLACE ilike '%magic eden%') AND SUCCEEDED = 'TRUE'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10