select

date(HOUR) as day,

TOKEN_ADDRESS,

symbol,

avg(PRICE) as price_usd

from

optimism.core.fact_hourly_token_prices

group by

day,

TOKEN_ADDRESS, symbol

), buys as (

select

date(BLOCK_TIMESTAMP) as day,

ORIGIN_TO_ADDRESS as buyer,

CONTRACT_ADDRESS as token,

sum(RAW_AMOUNT) as amount

from

optimism.core.fact_token_transfers

group by

1, 2, 3

), sells as (

select

date(BLOCK_TIMESTAMP) as day,

ORIGIN_FROM_ADDRESS as seller,

CONTRACT_ADDRESS as token,

sum(RAW_AMOUNT) as amount

from

optimism.core.fact_token_transfers

group by

1, 2, 3

), assets as (

select

day,

buyer as wallet,

token,