MLDZMNdom_user1.1
    Updated 2023-08-14

    with t1 as (select
    distinct signers[0] as wallet,
    case
    when logs.value ilike '%.bonk%' then '.bonk'
    when logs.value ilike '%.poor%' then '.poor'
    when logs.value ilike '%.moon%' then '.moon'
    when logs.value ilike '%.abc%' then '.abc'
    else 'Other TLDs'
    end as TLDs,
    count(distinct tx_id) as no_domains
    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'
    group by 1,2),

    t2 as (select
    wallet,
    count(distinct TLDs) as no_TLDs
    from t1
    group by 1

    )

    select
    case
    when no_TLDs=1 then '1 TLD'
    when no_TLDs=2 then '2 TLDs'
    Run a query to Download Data