mrwildcatpyth blast daily contracts
Updated 2025-02-11
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 pyth avalanche daily contracts @ https://flipsidecrypto.xyz/edit/queries/7490e035-9573-40c1-9e86-c21240832848
-- forked from pyth optimism daily contracts @ https://flipsidecrypto.xyz/edit/queries/cb8109d5-342a-45be-9c56-e34de9e4820b
-- forked from pyth gnosis daily contracts @ https://flipsidecrypto.xyz/edit/queries/30ad3fa2-0f45-480b-84ce-c762d12875cd
-- forked from pyth base daily contracts @ https://flipsidecrypto.xyz/edit/queries/426d7fff-8330-4798-81f5-ad2dca82f1cb
-- forked from pyth bsc daily contracts copy @ https://flipsidecrypto.xyz/edit/queries/03e0cfcc-b41f-4874-a363-e14e2b0a33c8
-- forked from pyth aurora daily contracts @ https://flipsidecrypto.xyz/edit/queries/4bf15f8a-3606-4aa4-9a19-3270b6d95078
WITH daily_contracts AS (
SELECT DATE_TRUNC('day', block_timestamp) as date,
COUNT(DISTINCT from_address) as daily_contracts
FROM blast.core.fact_traces
WHERE to_address = LOWER('0xA2aa501b19aff244D90cc15a4Cf739D2725B5729')
AND block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '6 months'
GROUP BY 1
)
SELECT
date,
daily_contracts,
AVG(daily_contracts) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_day_avg,
SUM(daily_contracts) OVER (ORDER BY date) as cumulative_contracts
FROM daily_contracts
ORDER BY date
QueryRunArchived: QueryRun has been archived