0xHaM-dContract Deployers Over Time copy copy copy
Updated 2024-07-03Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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