SELECT sender,

count(DISTINCT tx_hash) as n_txns

FROM ethereum.uniswapv3.ez_swaps

WHERE pool_address = LOWER('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')

AND block_timestamp::date BETWEEN CURRENT_DATE- interval '1 month , 1 day' AND CURRENT_DATE-1

GROUP BY 1

),

swappers_catagories as(

SELECT sender as senderr,

CASE when n_txns >300 then 'high active'

WHEN n_txns>30 AND n_txns<=300 then 'moderately active'

when n_txns <=30 then 'low active' end as catagory

FROM swapper_activty

SELECT

date_trunc('day', block_timestamp) as date,

y.catagory,

sum(CASE WHEN AMOUNT0_ADJUSTED >0 THEN AMOUNT0_ADJUSTED END) AS VOLUME_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_IN,

avg(CASE WHEN AMOUNT0_ADJUSTED <0 THEN AMOUNT0_ADJUSTED END) AS avg_OUT,

count(DISTINCT tx_hash) as n_txns,

count(DISTINCT sender) as N_swapers,

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 x

LEFT JOIN swappers_catagories y

ON x.sender = y.senderr

WHERE pool_address = LOWER('0x88e6A0c2dDD26FEEb64F039a2c41296FcB3f5640')

AND date <= CURRENT_DATE-1 AND date>= CURRENT_DATE-30

GROUP BY 1, 2

ORDER by date DESC