SELECT

date_trunc('d', block_timestamp) as date,

count(DISTINCT origin_from_address) as DAU

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address IN (SELECT address FROM arbitrum.core.dim_labels where project_name ilike '%beefy%')

AND date >= dateadd('month', -12, current_date)

GROUP BY date

,new as (

SELECT

date_trunc('d', first_tx) as date,

count(DISTINCT user) as new_user

FROM (

SELECT

origin_from_address as user,

min(block_timestamp) as first_tx

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address IN (SELECT address FROM arbitrum.core.dim_labels where project_name ilike '%beefy%')

AND block_timestamp::DATE >= '2021-07-22'

GROUP BY 1)

WHERE date >= dateadd('month', -12, current_date)

GROUP BY 1

SELECT

a.date,

Dau,

coalesce(new_user,0) as n_new_user,

Dau - n_new_user as recurring_user,

100 * n_new_user / Dau as new_user_percent,

100 * recurring_user / Dau as recurring_user_percent

FROM DAU_u a

LEFT JOIN new b using(date)

ORDER BY 1 DESC