SIGNERS[0] as user ,

INNER_INSTRUCTION:instructions[0]:parsed:info:"amount"/pow(10,5) as amounts

from solana.core.fact_events

where program_id = 'TLDHkysf5pCnKsVA4gXpNvmy7psXLPEu4LAdDJthT9S'

and block_timestamp::date >= '2022-10-22'

and SUCCEEDED = 'true')

,

final as (select date(block_timestamp) as date,

case when c.value ilike '%Buying%' then REPLACE(c.value, 'Program log: Buying or extending renewable domain ')::string

when c.value like '%creating new%' then REPLACE(c.value, 'Program log: creating new domain ')::string

when c.value like '%Creating new%' then REPLACE(c.value, 'Program log: Creating new domain ')::string

when c.value like '%Program log: Claiming an expirable domain%' then REPLACE(c.value, 'Program log: Claiming an expirable domain ')::string

else c.value end as domain,

user,

c.value,

a.tx_id ,

from solana.core.fact_transactions a join tx b on a.tx_id = b.tx_id, lateral flatten(input => LOG_MESSAGES) c

where block_timestamp::date >= '2022-10-22'

and (c.value ilike '%Buying%'

or c.value ilike '%Creating new%'

or c.value ilike '%Claiming%'))

select

case when domain ilike '%bonk%' then 'Bonk'

when domain ilike '%abc%' then 'ABC'

when domain ilike '%poor%' then 'Poor'

when domain ilike '%moon%' then 'Moon'

when domain ilike '%.all%' then 'All' end as type,

count(DISTINCT(user)) as users,

count(DISTINCT(tx_id)) as total_tx,

count(domain) as domains

from final

group by 1

having type is not null