select

date_trunc('hour',BLOCK_TIMESTAMP) AS DATE,

SUM(amount_out) AS total_bought

from ethereum.core.ez_dex_swaps

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

AND token_out=lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

group by 1

),

sells AS(

select

date_trunc('hour',BLOCK_TIMESTAMP) AS DATE,

SUM(amount_in) AS total_sold

from ethereum.core.ez_dex_swaps

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

AND token_in=lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

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 TOKEN_ADDRESS=lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

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)