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%'))

,

average as (select trunc(date,'week') as weekly,

user,

count(DISTINCT(tx_id)) as total_tx,

count(DISTINCT(domain)) as domains

from final

group by 1,2)

select

count(DISTINCT(user)) as users,

sum(total_tx) as transaction,

avg(domains) as avg_domain_per_user,

sum(domains) as domain