-- forked from 01 - new users monthly @ https://flipsidecrypto.xyz/edit/queries/8ceba136-b558-46d8-8598-abf79e95ffab

with

new_users AS

SELECT

min( a.block_timestamp ) AS min_date

, a.origin_from_address AS new_users

FROM

ethereum.core.ez_dex_swaps a

WHERE

a.platform LIKE 'uniswap%'

GROUP BY 2

ORDER BY 1 DESC

,

type AS

SELECT

a.origin_from_address AS "users"

, count( DISTINCT date_trunc( 'month' , a.block_timestamp ) ) AS "active days"

FROM

ethereum.core.ez_dex_swaps a

INNER JOIN

new_users b

ON a.origin_from_address = b.new_users

WHERE

a.platform LIKE 'uniswap%'

AND b.min_date > '2023-01-01'

AND b.min_date < dateadd( 'month' , -1 , current_date )

AND a.amount_in_usd IS NOT NULL

GROUP BY 1

ORDER BY 2 DESC