event_names,

sum(trxs) as sum_trxs

from (select date_trunc('day', block_timestamp) as days,

event_name,

count(distinct(tx_hash)) as trxs,

case when event_name ilike 'Remove%' then 'removing Liquidity'

when event_name ilike 'Add%' then 'providing Liquidity'

when event_name ilike 'Swap%' then 'swaping'

end as event_names

from ethereum.core.fact_event_logs

where contract_address in (select contract_address

from (select contract_address,

pool_name,

count(*)

FROM ethereum.core.ez_dex_swaps

where block_timestamp >= '2022-01-01'

and symbol_in in ('rETH')

group by 1, 2

)

)

and (event_name ilike 'add%' or

event_name ilike 'remove%' or

event_name ilike 'swap%')

and block_timestamp >= '2022-01-01'

group by 1,2

)

where event_names != 'swaping'

group by 1,2