select

distinct block_timestamp,

tx_id,

origin_address,

row_number() over (

partition by origin_address

order by

block_timestamp

) as rownumber

from

ethereum.udm_events

where

contract_address = '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5'

and origin_function_name ilike '%register%'

),

btc_price as (

select

date_trunc('week', hour) as week,

avg(price) as price

from

ethereum.token_prices_hourly

where

symbol = 'WETH'

group by

week

select

date_trunc('week', block_timestamp) as blocktime,

count(tx_id) as number_of_registrations,

count_if(rownumber = 1) as address_register_first_domain,

count(distinct origin_address) - address_register_first_domain as address_register_additional_domain,

address_register_first_domain / address_register_additional_domain as ratio,

address_register_first_domain / number_of_registrations as ratio1,

price

from