solana as (

SELECT

distinct INSTRUCTION:accounts[1] as users,

min(block_timestamp) as debut

from solana.core.fact_events

join solana.core.dim_labels on program_id = address

WHERE label_type IN ('dex', 'defi')

GROUP BY 1

),

solana2 as (

SELECT

trunc (debut,'day') as date,

case when debut < CURRENT_DATE-INTERVAL '1 MONTH' then 'Past activity' else 'Present activity' end as period,

count(distinct users) as new_users,

sum(new_users) over (partition by period order by date) as cum_new_users

from solana where debut >= CURRENT_DATE-INTERVAL '2 MONTHS'

GROUP BY 1, 2

order by 1 asc

),

ethereum as (

SELECT

distinct origin_from_address as users,

min(block_timestamp) as debut

from ethereum.core.fact_event_logs

join ethereum.core.dim_labels on contract_address = address

WHERE label_type IN ('dex', 'defi')

GROUP BY 1

),

ethereum2 as (

SELECT

trunc (debut,'day') as date,

case when debut < CURRENT_DATE-INTERVAL '1 MONTH' then 'Past activity' else 'Present activity' end as period,

count(distinct users) as new_users,

sum(new_users) over (partition by period order by date) as cum_new_users

from ethereum where debut >= CURRENT_DATE-INTERVAL '2 MONTHS'