SELECT

block_timestamp::date as date,

AVG(swap_to_amount/swap_from_amount) as fx_rate

FROM solana.fact_swaps

WHERE succeeded = TRUE

AND SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112'

AND SWAP_TO_MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

AND SWAP_TO_AMOUNT > 0.1

AND SWAP_FROM_AMOUNT > 0.1

GROUP BY 1

ORDER BY 1

),

solana_nfts as (

SELECT

fns.block_timestamp,

fns.tx_id,

mint as token_id,

sales_amount*fx_rate as usd_price

FROM

solana.fact_nft_sales fns

LEFT JOIN prices p ON p.date=fns.block_timestamp::date

WHERE fns.marketplace like 'magic eden %'

)

SELECT

block_timestamp::date as magiceden_date,

sum(usd_price) as magiceden_volume,

count(tx_id) as magiceden_txs,

avg(usd_price) as magiceden_avg_price

FROM

solana_nfts

WHERE usd_price < 1000000000

group by 1

order by 1