0xKofitrending 2
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 p2 AS (
SELECT
TO_ADDRESS AS contract,
NAME AS contract_name,
COUNT(DISTINCT TX_HASH) AS txns,
COUNT(DISTINCT FROM_ADDRESS) AS active_accounts,
SUM(GAS_PRICE_PAID/1e9) AS gas_spend
FROM arbitrum.core.fact_transactions t
INNER JOIN arbitrum.core.dim_contracts c ON c.ADDRESS = t.TO_ADDRESS
WHERE BLOCK_TIMESTAMP >= current_timestamp - interval '1 day'
GROUP BY 1,2
)
, p1 AS (
SELECT
TO_ADDRESS AS contract,
NAME AS contract_name,
COUNT(DISTINCT TX_HASH) AS txns,
COUNT(DISTINCT FROM_ADDRESS) AS active_accounts,
SUM(GAS_PRICE_PAID/1e9) AS gas_spend
FROM arbitrum.core.fact_transactions t
INNER JOIN arbitrum.core.dim_contracts c ON c.ADDRESS = t.TO_ADDRESS
WHERE BLOCK_TIMESTAMP < current_timestamp - interval '1 day'
AND BLOCK_TIMESTAMP >= current_timestamp - interval '2 day'
GROUP BY 1,2
)
SELECT
p2.contract,
p2.contract_name,
p2.active_accounts,
100 *(p2.active_accounts - p1.active_accounts)/(p1.active_accounts) as accounts_percentage_growth,
p2.txns,
100 *(p2.txns - p1.txns)/(p1.txns) as txns_percentage_growth,
p2.gas_spend,
100 *(p2.gas_spend - p1.gas_spend)/(p1.gas_spend) as gas_spend_percentage_growth
Run a query to Download Data