contracts as (

select

distinct el.contract_address as contract,

case

when dl.project_name is not null then dl.project_name

when el.contract_address is null then 'transfer'

when el.contract_address = '0xf9ad3f5eab7e9214387c75d58ce40d3a6d05b930' then 'project galaxy'

when el.contract_address in ('0x3b3e4b4741e91af52d0e9ad8660573e951c88524','0x51af494f1b4d3f77835951fa827d66fc4a18dae8','0xc22fbb3133df781e6c25ea6acebe2d2bb8cea2f9') then 'woofi'

when el.contract_address = '0x4f92ae4960a6ac49fa88bcf9d6d4b8c53f626a55' then 'holograph operator'

when el.contract_address = '0xa5972eee0c9b5bbb89a5b16d1d65f94c9ef25166' then 'layerzero' -- bridge to aptos

else el.contract_address

end as dapp

from

avalanche.core.fact_decoded_event_logs el

left join

avalanche.core.dim_labels dl

on el.contract_address = dl.address

OR (el.contract_address IS NULL AND dl.address IS NULL)

),

base as (

SELECT

DISTINCT

date_trunc('MONTH', date(ft.block_timestamp)) as month_active,

ft.from_address as address

FROM

avalanche.core.fact_transactions ft

left join

avalanche.core.fact_decoded_event_logs el

on ft.tx_hash = el.tx_hash

left join

contracts c

on el.contract_address = c.contract or (el.contract_address is null and c.contract is null)

where c.dapp = '{{Dapp}}'

),

user_cohort as (