mansaDeposit Type
Updated 2022-07-17
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
›
⌄
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,
locked_date - date(block_timestamp) as duration
FROM
optimism.core.fact_event_logs
WHERE
topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'
)
SELECT
sum(case when deposit_type = 0 then 1 end) as CVELO,
sum(case when deposit_type = 1 then 1 end) as DAOveNFTs,
sum(case when deposit_type = 2 then 1 end) as VTVELO,
sum(case when deposit_type = 3 then 1 end) as lockedveNFTs,
sum(case when deposit_type = 4 then 1 end) as GPVELO
FROM
base
WHERE date(block_timestamp) >= CURRENT_DATE - 30
Run a query to Download Data