Total Relocked

feyikemiTotal Relocked

Updated 2024-11-05

-- WITH unlocks AS (

-- -- Calculate the total unlocked volume per account

-- SELECT

-- ACCOUNT_ADDRESS,

-- SUM(OLAS_AMOUNT) AS total_unlocked_volume

-- FROM

-- crosschain.olas.ez_olas_locking

-- WHERE

-- UNLOCK_TIMESTAMP IS NOT NULL

-- GROUP BY

-- ACCOUNT_ADDRESS

-- ),

-- relocks AS (

-- -- Calculate the relocked veOLAS volume per account after any prior unlock

-- SELECT

-- t.ACCOUNT_ADDRESS,

-- SUM(t.OLAS_AMOUNT) AS total_relocked_veolas

-- FROM

-- crosschain.olas.ez_olas_locking t

-- JOIN

-- crosschain.olas.ez_olas_locking u

-- ON t.ACCOUNT_ADDRESS = u.ACCOUNT_ADDRESS

-- AND t.UNLOCK_TIMESTAMP > u.UNLOCK_TIMESTAMP

-- AND u.OLAS_AMOUNT > 0 -- Ensuring we're counting only positive OLAS_AMOUNT as prior unlocks

-- GROUP BY

-- t.ACCOUNT_ADDRESS

-- )

-- -- Final calculation for the percentage of relocked veOLAS out of total unlocked veOLAS

-- SELECT

-- COALESCE(SUM(unlocks.total_unlocked_volume), 0) AS total_volume_unlocked,

-- COALESCE(SUM(relocks.total_relocked_veolas), 0) AS total_volume_relocked,

-- CASE

-- WHEN SUM(unlocks.total_unlocked_volume) > 0

-- THEN (SUM(relocks.total_relocked_veolas) / SUM(unlocks.total_unlocked_volume)) * 100

-- ELSE 0

QueryRunArchived: QueryRun has been archived

flipside