SELECT date_trunc('{{interval}}', block_timestamp) as date,

sum(CASE when AMOUNT0_ADJUSTED > 0 then AMOUNT0_ADJUSTED end) as volume_in,

avg(CASE when AMOUNT0_ADJUSTED > 0 then AMOUNT0_ADJUSTED end) as avg_vol_in,

sum(CASE when AMOUNT0_ADJUSTED < 0 then AMOUNT0_ADJUSTED end) as volume_out,

avg(CASE when AMOUNT0_ADJUSTED < 0 then AMOUNT0_ADJUSTED end) as avg_vol_out,

count(DISTINCT tx_hash) AS n_txns,

count(DISTINCT sender) as n_swappers,

volume_in-volume_out as total_volume,

volume_in+volume_out as net_volume,

CASE when net_volume <0 then 'a' else 'b' end as colour

FROM ethereum.uniswapv3.ez_swaps

WHERE pool_address = {{'pool_address'}} -- '0x5777d92f208679db4b9778590fa3cab3ac9e2168' = DAI/USDC

AND date BETWEEN CURRENT_DATE- interval '{{last_period}} , 1 day' AND CURRENT_DATE-1

GROUP BY 1

ORDER by date DESC