from ethereum.events_emitted

where contract_address='0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85' and

event_name = 'NameRegistered' and tx_succeeded = 'TRUE'

group by 1),

sale as (select event_from, count(distinct token_id) as sale

from ethereum.nft_events

where contract_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85' and event_type = 'sale'

group by 1),

buy as (select event_to, count(distinct token_id) as buy

from ethereum.nft_events

where contract_address = '0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85' and event_type = 'sale'

group by 1),

register_sold as (select a.tx_from_address, b.sale, case when b.sale is null then 0 else b.sale end as sales, (a.register_domain - sales) as domains_owned

from register a

left outer join sale b on a.tx_from_address = b.event_from),

register_buy as (select a.tx_from_address, (a.domains_owned + b.buy) as domain_owned, case

when domain_owned = 1 then 'One_Domain_owned'

else 'Multiple_domains' end as own

from register_sold a

left outer join buy b on a.tx_from_address = b.event_to)

select own, count(tx_from_address)

from register_buy

where domain_owned is not NULL

group by 1