MLDZMNdom_user3
    Updated 2023-08-14
    with t1 as (select
    distinct signers[0] as users
    from solana.core.fact_transactions s
    join (select tx_id
    from solana.core.fact_events where succeeded = True
    and program_id = 'TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S') events
    using(tx_id)
    join lateral flatten (input => log_messages) logs
    where (logs.value ilike 'Program log: Creating new domain%' --.bonk and .poor has no extend option and cretaed once
    or logs.value ilike 'Program log: Buying or extending renewable domain%' --.abc and .moon has extend option
    or logs.value ilike 'Program log: Claiming an expirable domain%')
    and BLOCK_TIMESTAMP>='2022-10-21'
    and signers[0] <> '2rWVMwtTQGDrG875uHmZWKot7zEUcqEjyyafh9kuVoFc'
    )

    select
    block_timestamp::date as date,
    l.LABEL_TYPE as type,
    count(distinct t.signers[0]) as sender,
    count(distinct t.tx_id) as no_txn
    from solana.core.fact_events t join solana.core.dim_labels l on t.PROGRAM_ID = l.address
    where t.block_timestamp>= current_date-90
    and l.label_subtype != 'token_contract'
    and l.LABEL_TYPE in ('defi','nft','dex','dapp')
    and l.label != 'solana'
    and t.succeeded = TRUE
    and t.signers[0] in (select users from t1)
    group by 1,2
    Run a query to Download Data