WITH NAMES AS (

SELECT TX_HASH ,

DATE_TRUNC('DAY',BLOCK_TIMESTAMP) AS DATE,

EVENT_DATA:"domain_name" AS domain_name,

EVENT_DATA:"registration_fee_octas"/1E8 AS amount ,

DATEADD(s, EVENT_DATA:"expiration_time_secs", '1970-01-01 00:00:00.000') as expiration_Date

FROM aptos.core.fact_events

WHERE PAYLOAD_FUNCTION = '0x867ed1f6bf916171b1de3ee92849b8978b7d1b9e0a8cc982a3d19d535dfd9c0c::router::register_domain'

AND EVENT_RESOURCE = 'RegisterNameEvent'

),

ADDRESSES AS (

SELECT ACCOUNT_ADDRESS as address,

TX_HASH

FROM aptos.core.fact_events

WHERE PAYLOAD_FUNCTION = '0x867ed1f6bf916171b1de3ee92849b8978b7d1b9e0a8cc982a3d19d535dfd9c0c::router::register_domain'

AND EVENT_RESOURCE = 'WithdrawEvent'

),

apt_price as (SELECT

DATE_TRUNC('DAY',HOUR) AS date,SYMBOL,avg(PRICE) as price_

FROM aptos.price.ez_hourly_token_prices

where SYMBOL= 'APT'

group by date,SYMBOl

select count (distinct b.address) as addresses,

count (distinct b.TX_HASH) as transactions ,

a.DATE,

count (distinct domain_name) as domain_names,

sum(amount) as total_amount,

SYMBOL,

from names a left join ADDRESSES b on a.TX_HASH=b.TX_HASH

left join apt_price c on a.date=c.date

group by 3,6,7