with

active_users as (

SELECT

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

count(DISTINCT tx_hash) as "Number of swaps",

count(DISTINCT origin_from_address) as "Number of Active users"

FROM ethereum.core.ez_dex_swaps

WHERE platform IN ('uniswap-v2', 'uniswap-v3')

AND date > DATEADD('{{time}}', -{{last_n_period}}, current_date)

AND date < current_date

GROUP BY 1

),

new_user as (

SELECT

date_trunc('{{interval}}', first_tx) as date,

count(DISTINCT origin_from_address) as new_users,

sum(new_users)over(ORDER BY date) as cum_new_users

FROM (

SELECT

origin_from_address,

min(block_timestamp) as first_tx

FROM ethereum.core.ez_dex_swaps

WHERE platform IN ('uniswap-v2', 'uniswap-v3')

AND block_timestamp > DATEADD('{{time}}', -{{last_n_period}}, current_date)

AND block_timestamp < current_date

GROUP BY 1 )

GROUP BY 1

SELECT

a.date,

"Number of Active users",

new_users,

100*new_users/"Number of Active users" as "new_user per active_user"

FROM active_users a JOIN new_user b using (date)