contract_creation_tx as (

select

tx_hash,

block_timestamp,

from_address as deployer,

tx_json:receipt:contractAddress as contract,

-- tx_json,

tx_fee

from

base.goerli.fact_transactions

where

to_address is null

and block_timestamp >= to_date('{{start_date}}')

and block_timestamp <= add_months(

to_date('{{start_date}}'),

{{duration_in_months}}

)

and status = 'SUCCESS'

-- limit 1

-- and tx_json::string like '%Upgrade%'

),

deployers_stats as (

select

deployer

, label_type

, coalesce(project_name, left(deployer,6)) as project

, address_name

, count(tx_hash) as deploy_count

, rank() over(order by deploy_count DESC ) as rank

from

contract_creation_tx ct

left outer join optimism.core.dim_labels lb on ct.deployer = lb.address