WITH INPUT_DATA 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 TOKEN_ID, 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' ), OUTPUT_DATA AS ( SELECT DATEDIFF(SECOND, BLOCK_TIMESTAMP, LOCKED_DATE)/86400 LOCK_DURATION FROM INPUT_DATA ) SELECT 1 "ROW NO.", '<= 1 MONTH' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION <= 30 UNION ALL SELECT 2 "ROW NO.", '> 1 MONTH AND <= 6 MONTHS' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION > 30 AND LOCK_DURATION <= 180 UNION ALL SELECT 3 "ROW NO.", '> 6 MONTHS AND <= 1 YEAR' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION > 180 AND LOCK_DURATION <= 365 UNION ALL SELECT 4 "ROW NO.", '> 1 YEAR AND <= 2 YEARs' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION > 365 AND LOCK_DURATION <= 730 UNION ALL SELECT 5 "ROW NO.", '> 2 YEARs AND <= 3 YEARs' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION > 730 AND LOCK_DURATION <= 1095 UNION ALL SELECT 6 "ROW NO.", '> 3 YEARS AND <= 4 YEARs' "DURATION", COUNT(LOCK_DURATION) "NO. OF VELO LOCKING TXs" FROM OUTPUT_DATA WHERE LOCK_DURATION > 1095 AND LOCK_DURATION <= 1460 ORDER BY "ROW NO."

Optimism Bounties: Velodrome Locks