KARTODVELO locked
Updated 2023-05-09
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 base AS (
SELECT
*,
CONCAT('0x', SUBSTR(topics [1] :: STRING, 27, 42)) AS provider,
ethereum.public.udf_hex_to_int(
topics [2] :: STRING
) :: DATE AS locked_date,
regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,
ethereum.public.udf_hex_to_int(
segmented_data [0] :: STRING
) AS tokenID,
ethereum.public.udf_hex_to_int(
segmented_data [1] :: STRING
)/pow(10,18) AS VALUE,
ethereum.public.udf_hex_to_int(
segmented_data [2] :: STRING
) AS deposit_type,
ethereum.public.udf_hex_to_int(
segmented_data [3] :: STRING
) AS TIMESTAMP
FROM
optimism.core.fact_event_logs
WHERE
topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'
)
SELECT
block_timestamp::date as date,
count(DISTINCT tx_hash) as "Lock txs count",
count(DISTINCT origin_from_address) as "User count",
sum(VALUE) as "VELO Amount",
sum("Lock txs count") over (order by date asc) as "Cumulative txs count",
avg(value) as "Avg. VELO locked value",
sum("User count") over (order by date asc) as "Cumulative users",
sum("VELO Amount") over (order by date asc) as "Cumulative locked amount"
FROM base
where provider not in ('0x5d5bea9f0fc13d967511668a60a3369fd53f784f')
Run a query to Download Data