select date_trunc('hour', BLOCK_TIMESTAMP) as "Date_Hour",

'Buy' as "Type",

count(*) as "Swaps",

count(distinct ORIGIN_FROM_ADDRESS) as "Users",

sum(AMOUNT_OUT) as "Volume"

from base.defi.ez_dex_swaps

where TOKEN_OUT = lower('0xac1bd2486aaf3b5c0fc3fd868558b082a531b2b4')

group by 1, 2

union all

select date_trunc('hour', BLOCK_TIMESTAMP) as "Date_Hour",

'Sell' as "Type",

count(*) as "Swaps",

count(distinct ORIGIN_FROM_ADDRESS) as "Users",

-sum(AMOUNT_IN) as "Volume"

from base.defi.ez_dex_swaps

where TOKEN_IN = lower('0xac1bd2486aaf3b5c0fc3fd868558b082a531b2b4')

group by 1, 2

),

prices AS (

select date_trunc('hour', hour) as "Date_Hour",

PRICE

from base.price.ez_prices_hourly

where TOKEN_ADDRESS = lower('0xac1bd2486aaf3b5c0fc3fd868558b082a531b2b4')

SELECT date_trunc('day', s."Date_Hour") as "Date",

s."Type",

sum(s."Swaps") as "Total_Swaps",

sum(s."Users") as "Total_Users",

sum(s."Volume") as "Total_Volume",

avg(p.PRICE) as "Avg_Price",

sum(s."Volume" * p.PRICE) as "Total_Volume_in_USD"

FROM swaps s

JOIN prices p ON s."Date_Hour" = p."Date_Hour"

GROUP BY date_trunc('day', s."Date_Hour"), s."Type"