SELECT

ACCOUNT_ADDRESS,

UNLOCK_TIMESTAMP,

SUM(OLAS_AMOUNT) AS total_deposited

FROM crosschain.olas.ez_olas_locking

WHERE EVENT_NAME = 'Deposit'

GROUP BY ACCOUNT_ADDRESS, UNLOCK_TIMESTAMP

),

withdraws AS (

SELECT

ACCOUNT_ADDRESS,

SUM(OLAS_AMOUNT) AS total_withdrawn

FROM crosschain.olas.ez_olas_locking

WHERE EVENT_NAME = 'Withdraw'

GROUP BY ACCOUNT_ADDRESS

SELECT

COALESCE(SUM(d.total_deposited - COALESCE(w.total_withdrawn, 0)), 0) AS total_currently_locked_olas

FROM

deposits d

LEFT JOIN

withdraws w ON d.ACCOUNT_ADDRESS = w.ACCOUNT_ADDRESS

WHERE

d.UNLOCK_TIMESTAMP > CURRENT_TIMESTAMP -- Only include deposits still within their lock period

AND d.total_deposited > COALESCE(w.total_withdrawn, 0); -- Ensure there is a positive locked amount