select

date(HOUR) as the_day,

'ETH' as symbol,

avg(PRICE) as price

from

ethereum.core.fact_hourly_token_prices

where symbol='WETH'

group by

the_day, symbol

union all

select

date(HOUR) as the_day,

symbol,

avg(PRICE) as price

from

optimism.core.fact_hourly_token_prices

where symbol='OP'

group by

the_day, symbol

select

-- date_trunc('day',block_timestamp) as the_date,

b.ADDRESS_NAME,

sum(a.PRICE*c.price) as "Price USD",

count(distinct TX_HASH) as "Number of Transactions"

-- sum("Price USD") over (partition by b.ADDRESS_NAME order by the_date asc) as "Cumulative Price USD",

-- count("Number of Transactions") over (partition by b.ADDRESS_NAME order by the_date asc) as "Cumulative Number of Transactions"

from

optimism.core.ez_nft_sales a join optimism.core.dim_labels b on a.NFT_ADDRESS=b.ADDRESS join weth c on date(block_timestamp)=c.the_day and a.CURRENCY_SYMBOL=c.symbol

where

PLATFORM_NAME='quixotic'

group by

b.ADDRESS_NAME

-- , the_date

order by