with oppricet as (

select hour::date as date,

avg (price) as OPPrice

from optimism.core.fact_hourly_token_prices

where symbol = 'OP'

group by 1),

ETHPricet as (

select hour::date as date,

avg (price) as ETHPrice

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1),

SOLPricet as (

select block_timestamp::date as date,

avg (swap_to_amount/swap_from_amount) as SOLprice

from solana.fact_swaps

where swap_from_mint = 'So11111111111111111111111111111111111111112' --SOL

and swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT

and swap_to_amount > 0

and swap_from_amount > 0

and succeeded = 'TRUE'

group by 1),

maintable as (

select 'Quixotic' as platform,

buyer_address as buyer,

count (distinct tx_hash) as Sales_Count,

count (distinct tokenid) as NFTS_Count,

sum (case when currency_symbol ='ETH' then price*ETHPrice when currency_symbol = 'OP' then price*OPPrice end) as Total_Volume

from optimism.core.ez_nft_sales t1 join oppricet t2 on t1.block_timestamp::date = t2.date

join ethpricet t3 on t1.block_timestamp::date = t3.date

where platform_name = 'quixotic' and block_timestamp >= '2022-07-01'

group by 1,2