select

date_trunc('hour',BLOCK_TIMESTAMP) AS DATE,

SUM(amount_out) AS total_bought

from optimism.core.ez_dex_swaps

WHERE DATE BETWEEN {{start_date}} AND {{end_date}}

AND token_out=lower({{token_address}})

group by 1

),

sells AS(

select

date_trunc('hour',BLOCK_TIMESTAMP) AS DATE,

SUM(amount_in) AS total_sold

from optimism.core.ez_dex_swaps

WHERE DATE BETWEEN {{start_date}} AND {{end_date}}

AND token_in=lower({{token_address}})

group by 1

),

price AS(

select

date_trunc('hour', HOUR) AS DATE,

Symbol,

avg(price) as price

from crosschain.core.ez_hourly_prices

WHERE DATE BETWEEN {{start_date}} AND {{end_date}}

AND SYMBOL={{token}}

group by 1,2

b.date,

b.total_bought,

s.total_sold,

p.price

FROM buys b

LEFT JOIN sells s USING(DATE)

LEFT JOIN price p USING(DATE)