with ens_domain_expire_date as (
select
distinct
block_timestamp,
event_inputs:id as id,
event_inputs:expires::int as expire_date,
event_name,
datediff('day',block_timestamp,to_timestamp(expire_date)) as days_until_expire
from
ethereum.events_emitted
where
event_name in ('NameRegistered', 'NameRenewed')
and
contract_address in ('0x57f1887a8bf19b14fc0df6fd9b2acc9af147ea85', '0x283af0b28c62c092c9727f1ee09c02ca627eb7f5')
-- and block_timestamp > getdate() - interval'6 months'
and days_until_expire > 0
)
select
round(days_until_expire,-1) as rounded_days_until_expire,
event_name,
count(days_until_expire) as frequency
from
ens_domain_expire_date
where rounded_days_until_expire < 365 * 5
group by rounded_days_until_expire, event_name
order by rounded_days_until_expire