DAILY | Total Contract | New Contract | Active Contract | |
|---|---|---|---|---|
1 | 2024-12-05 00:00:00.000 | 2 | 2 | 0 |
2 | 2024-12-09 00:00:00.000 | 1 | 1 | 0 |
3 | 2024-12-12 00:00:00.000 | 3 | 3 | 0 |
4 | 2024-12-13 00:00:00.000 | 1 | 0 | 1 |
5 | 2024-12-14 00:00:00.000 | 1 | 0 | 1 |
6 | 2024-12-15 00:00:00.000 | 5 | 2 | 3 |
7 | 2024-12-16 00:00:00.000 | 5 | 0 | 5 |
8 | 2024-12-17 00:00:00.000 | 3 | 0 | 3 |
9 | 2024-12-18 00:00:00.000 | 1 | 0 | 1 |
10 | 2024-12-19 00:00:00.000 | 3 | 0 | 3 |
11 | 2024-12-20 00:00:00.000 | 4 | 0 | 4 |
12 | 2024-12-21 00:00:00.000 | 1 | 0 | 1 |
13 | 2024-12-23 00:00:00.000 | 1 | 0 | 1 |
14 | 2024-12-24 00:00:00.000 | 1 | 0 | 1 |
15 | 2024-12-25 00:00:00.000 | 1 | 0 | 1 |
16 | 2024-12-26 00:00:00.000 | 1 | 0 | 1 |
17 | 2024-12-27 00:00:00.000 | 1 | 0 | 1 |
18 | 2024-12-28 00:00:00.000 | 1 | 0 | 1 |
19 | 2024-12-29 00:00:00.000 | 1 | 0 | 1 |
20 | 2024-12-30 00:00:00.000 | 1 | 0 | 1 |
Movement Team9_2. Active Vs. New Contract
Updated 4 days ago
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 active_contract AS (
SELECT
TRUNC(block_timestamp, 'day') AS daily,
COUNT(DISTINCT payload_function) AS no_contract
FROM movement.core.fact_transactions
WHERE
tx_type = 'user_transaction'
GROUP BY 1
),
new AS (
SELECT
MIN(block_timestamp) AS time,
payload_function
FROM movement.core.fact_transactions
WHERE
tx_type = 'user_transaction'
GROUP BY 2
),
new_contract AS (
SELECT
TRUNC(time, 'day') AS daily,
COUNT(DISTINCT payload_function) AS "New"
FROM new
GROUP BY 1
)
SELECT
a.daily,
no_contract AS "Total Contract",
COALESCE(b."New", 0) AS "New Contract",
no_contract - COALESCE(b."New", 0) AS "Active Contract"
FROM active_contract a
LEFT OUTER JOIN new_contract b
ON a.daily = b.daily
ORDER BY 1;
Last run: 4 days ago
95
3KB
2s