DATE_TRUNC('day', BLOCK_TIMESTAMP) AS DAY,

TX_HASH,

ORIGIN_FROM_ADDRESS,

CONTRACT_ADDRESS,

VELO_AMOUNT,

VELO_AMOUNT_USD,

DATEDIFF('days',BLOCK_TIMESTAMP,UNLOCK_DATE) AS DAYS_LOCKED,

DEPOSIT_TYPE,

(CASE

WHEN DAYS_LOCKED <= 7 THEN '1 Week'

WHEN DAYS_LOCKED <= 31 THEN '1 Month'

WHEN DAYS_LOCKED <= 365 THEN '1 Year'

WHEN DAYS_LOCKED <= 4 * 365 THEN '4 Years'

ELSE DAYS_LOCKED || ' days'

END) AS EXPIRE_TIME

FROM

optimism.velodrome.ez_velo_locks

WHERE VELO_ACTION = 'deposit')

SELECT

DAY,

EXPIRE_TIME,

COUNT(TX_HASH) AS TX_COUNT,

COUNT(DISTINCT(ORIGIN_FROM_ADDRESS)) AS ADDRESS,

SUM(TX_COUNT) OVER(PARTITION BY EXPIRE_TIME ORDER BY DAY ASC) AS RUNNING_TOTAL_TX,

SUM(VELO_AMOUNT) AS VELO_VOL,

SUM(VELO_VOL) OVER(PARTITION BY EXPIRE_TIME ORDER BY DAY ASC) AS RUNNING_TOTAL_VELO,

SUM(VELO_AMOUNT_USD) AS USD_VOL,

SUM(USD_VOL) OVER(PARTITION BY EXPIRE_TIME ORDER BY DAY ASC) AS RUNNING_TOTAL_USD

VELO_DEPOSIT

GROUP BY 1,2

ORDER BY 1, 3 DESC