0xHaM-dContract Deployers Over Time copy copy copy
    Updated 2024-07-03
    -- forked from 2023-07-30 06:13 PM @ https://flipsidecrypto.xyz/edit/queries/3938ca53-5487-449f-ab67-589ba8664443

    with contracts as (
    select
    distinct ADDRESS as creator,
    block_timestamp
    from axelar.core.fact_msg_attributes
    join axelar.core.dim_labels on address = ATTRIBUTE_VALUE
    join axelar.core.fact_transactions using(tx_id)
    WHERE LABEL_SUBTYPE!='token_contract'
    -- AND block_timestamp < trunc(current_date,'week')
    -- group by 1
    )
    ,
    deployers as (
    select creator, min(BLOCK_TIMESTAMP) as min_date
    from contracts
    group by 1
    )

    select
    date_trunc('quarter', min_date) as "Date",
    case
    when year(min_date) = '2023' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 23 )
    when year(min_date) = '2024' then concat('Q', quarter("Date"),'-','Months ','[',month("Date") ,' To ',month("Date") + 2,']','-', 24 )
    end as "Quarter",
    CASE when year(min_date) = '2023' then 'y.2023' else 'y.2024' end as year,
    count(creator) as "Deployers",
    round((("Deployers" / lag("Deployers") over (order by "Date")) - 1) * 100) AS "Change in Deployers in Percent"
    from deployers
    where min_date >= '2023-01-01'
    AND min_date < '2024-07-01'
    group by 1,2,3

    QueryRunArchived: QueryRun has been archived