with first_date_contract as (
select
tx:body:messages[0]:contract as contract,
min(block_timestamp) as min_date
from terra.core.fact_transactions
group by 1
)
, new_contract as (
SELECT
date_Trunc('day',min_date) as date,
count(distinct contract) as "New Contract Cnt",
sum("New Contract Cnt") over (order by date) as "Cumulative New Contract Cnt"
from first_date_contract
where min_date::date <= CURRENT_DATE - 1
group by 1
order by 1 asc
)
, active as (
SELECT
date_Trunc('day',block_timestamp) as date,
count(distinct tx:body:messages[0]:contract) as "Activ Contract Cnt"
from terra.core.fact_transactions
where block_timestamp::date <= CURRENT_DATE - 1
group by 1
)
SELECT
date,
"Activ Contract Cnt",
"New Contract Cnt",
"Cumulative New Contract Cnt"
FROM new_contract JOIN active USING(date)