princefarzamWhat durations are most common?
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
›
⌄
WITH Locked_txns 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, --Locked till this time
regexp_substr_all( SUBSTR(DATA, 3, len(DATA)), '.{64}' ) AS segmented_data, --for segmenting Data
ethereum.public.udf_hex_to_int( segmented_data[0] :: STRING ) AS tokenID, --TokenID
ethereum.public.udf_hex_to_int( segmented_data[1] :: STRING )/pow(10,18) AS VALUE, --Value($VELO)
ethereum.public.udf_hex_to_int( segmented_data[2] :: STRING ) AS deposit_type, --Deposit type
ethereum.public.udf_hex_to_int( segmented_data[3] :: STRING )::DATE AS TIMESTAMP, --Time of locking $VELO
DATEDIFF(day, TIMESTAMP, locked_date) AS Duration,
*
FROM optimism.core.fact_event_logs
WHERE topics [0] :: STRING = '0xff04ccafc360e16b67d682d17bd9503c4c6b9a131f6be6325762dc9ffc7de624'
AND TX_STATUS= 'SUCCESS')
SELECT
CASE
WHEN Duration <= 8 THEN 'ONE WEEK'
WHEN Duration > 8 AND Duration <= 31 THEN 'ONE MONTH'
WHEN Duration > 320 AND Duration <= 366 THEN 'ONE YEAR'
WHEN Duration > 1398 AND Duration <= 1460 THEN 'FOUR YEARS'
ELSE 'OTHER DURATIONS'
END AS COMMON_DURATIONS,
COUNT(DISTINCT TX_HASH) AS number_of_repeats
FROM Locked_txns
GROUP BY COMMON_DURATIONS
ORDER BY number_of_repeats DESC
Run a query to Download Data