from optimism.core.fact_hourly_token_prices

where symbol in ('OP')

group by 1, 2

select date_trunc('day', hour) as dt, 'ETH' as symbol, avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol in ('WETH')

group by 1, 2),

eth_price as (select date_trunc('day', hour) as dt, 'ETH' as symbol, avg(price) as avg_price

from ethereum. core.fact_hourly_token_prices

where symbol in ('WETH')

group by 1, 2),

quixotic_nft as (select date_trunc('day', s.block_timestamp) as dt,

case when s.currency_address = '0x4200000000000000000000000000000000000042' then 'OP' else s.currency_address end as symbol,

d.project_name, count(distinct s.tx_hash) as num_txs, count(distinct s.seller_address) as nft_sellers,

count(distinct s.buyer_address) as nft_buyers, sum(s.price) as nft_sales

from optimism.core.ez_nft_sales s

left join optimism.core.dim_labels d on

s.nft_address = d.address

where event_type = 'sale' and platform_name = 'quixotic'

group by 1, 2, 3),

quixotic_nft_daily as (select n.dt, n.symbol, project_name, num_txs, nft_sellers, nft_buyers, (nft_sales*avg_price) as nft_sales_usd

from quixotic_nft n

left join price_table p on

n.dt = p.dt and n.symbol = p.symbol),

quixotic_nft_daily_sum as (select dt, symbol, sum(num_txs) as total_num_txs, sum(nft_sellers) as total_nft_sellers,

sum(nft_buyers) as total_nft_buyers, sum(nft_sales_usd) as total_nft_sales_usd

from quixotic_nft_daily

group by 1, 2)

select a.dt, a.symbol, total_num_txs, total_nft_sellers, total_nft_buyers, total_nft_sales_usd, b.avg_price as eth_price