DEPLOYMENT_DATE | CALL_BIN_LABEL | CONTRACTS_DEPLOYED | UNIQUE_DEPLOYERS | SEVEN_DAY_MOVING_AVG | |
|---|---|---|---|---|---|
1 | 2025-01-22 00:00:00.000 | 25-50% | 243 | 5 | 329 |
2 | 2025-01-22 00:00:00.000 | 90-100% | 109 | 3 | 109 |
3 | 2025-01-22 00:00:00.000 | 50-75% | 620 | 3 | 350.5 |
4 | 2025-01-22 00:00:00.000 | 0-25% | 353 | 4 | 231 |
5 | 2025-01-22 00:00:00.000 | 75-90% | 320 | 3 | 260.666 |
6 | 2025-01-23 00:00:00.000 | 50-75% | 436 | 2 | 353.714 |
7 | 2025-01-23 00:00:00.000 | 75-90% | 307 | 3 | 325.333 |
8 | 2025-01-23 00:00:00.000 | 0-25% | 579 | 6 | 388.428 |
9 | 2025-01-23 00:00:00.000 | 90-100% | 197 | 6 | 307 |
10 | 2025-01-23 00:00:00.000 | 25-50% | 337 | 1 | 351.428 |
11 | 2025-01-24 00:00:00.000 | 0-25% | 486 | 4 | 369.285 |
12 | 2025-01-24 00:00:00.000 | 50-75% | 679 | 3 | 431.857 |
13 | 2025-01-24 00:00:00.000 | 75-90% | 388 | 2 | 425 |
14 | 2025-01-24 00:00:00.000 | 90-100% | 174 | 4 | 359.428 |
15 | 2025-01-24 00:00:00.000 | 25-50% | 332 | 3 | 363 |
16 | 2025-01-25 00:00:00.000 | 75-90% | 254 | 2 | 386.142 |
17 | 2025-01-25 00:00:00.000 | 90-100% | 140 | 5 | 336.714 |
18 | 2025-01-25 00:00:00.000 | 25-50% | 259 | 1 | 348.857 |
19 | 2025-01-25 00:00:00.000 | 0-25% | 333 | 2 | 349 |
20 | 2025-01-25 00:00:00.000 | 50-75% | 390 | 2 | 432.571 |
Sandeshcontracts_deployed
Updated 2025-02-21
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
›
⌄
WITH times_called AS (
-- Count how many times each contract was called
SELECT to_address, COUNT(DISTINCT tx_hash) AS times_called
FROM ronin.core.fact_traces
WHERE to_address IN (SELECT address FROM ronin.core.dim_contracts)
GROUP BY to_address
),
enriched_table AS (
-- Join contract metadata with times_called data
SELECT dc.*, COALESCE(tc.times_called, 0) AS times_called
FROM ronin.core.dim_contracts dc
LEFT JOIN times_called tc ON dc.address = tc.to_address
where creator_address not in (lower('0x02f376fe3e11634a866d2087f01427b09a235599'),lower('0x4990d27658070dd1f1a0cdd7411c3567676352a6')) -- smart wallet creation (waypoint)
),
contract_bins AS (
-- Assign percentile bins based on times_called
SELECT *,
CASE
WHEN times_called <= PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY times_called) OVER () THEN '0-25%'
WHEN times_called <= PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY times_called) OVER () THEN '25-50%'
WHEN times_called <= PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY times_called) OVER () THEN '50-75%'
WHEN times_called <= PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY times_called) OVER () THEN '75-90%'
ELSE '90-100%'
END AS call_bin_label
FROM enriched_table
),
daily_deployments AS (
-- Aggregate data by deployment date
SELECT
DATE_TRUNC('day', created_block_timestamp) AS deployment_date,call_bin_label,
COUNT(*) AS contracts_deployed,
COUNT(DISTINCT creator_address) AS unique_deployers
FROM contract_bins
WHERE created_block_timestamp >= DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY DATE_TRUNC('day', created_block_timestamp),call_bin_label
)
Last run: about 2 months ago
...
154
7KB
26s