select a.attribute_value as contract,

min(block_timestamp) as min_date

from terra.core.fact_msg_attributes a

left join terra.core.dim_address_labels b

on a.attribute_value = b.address

where a.attribute_key in ('contract', 'contract_address', '_contract_address', 'contract_addr', 'contract_name')

group by contract

),

t2 as (

select min_date::date as day,

iff(min_date >= '2023-01-14', 'After Announcement', 'Before Announcement') as timespan,

count(distinct contract) as new_contracts_count

from t

where min_date between date('2023-01-14') - interval '1 week' and date('2023-01-14') + interval '1 week'

group by 1, 2

timespan,

avg(new_contracts_count) as average_new_contracts_count

from t2

group by 1