SELECT

'0x' || SUBSTR(DATA, 27, 40) AS address,

utils.udf_hex_to_int(SUBSTR(DATA, 67, 64)) / POW(10, 18) AS amount

FROM berachain.testnet.fact_event_logs

WHERE contract_address = lower('0x30A3039675E5b5cbEA49d9a5eacbc11f9199B86D')

AND ORIGIN_FUNCTION_SIGNATURE = '0x617ba037' -- supply

AND '0x' || SUBSTR(PARSE_JSON(TOPICS)[1]::string, -40) = lower('0x0E4aaF1351de4c0264C5c7056Ef3777b41BD8e03') -- HONEY

AND address = origin_from_address

),

WithdrawData AS (

SELECT

'0x' || SUBSTR(PARSE_JSON(TOPICS)[2]::string, -40) AS address,

utils.udf_hex_to_int(SUBSTRING(DATA, 3, 64)) / POW(10, 18) AS amount

FROM berachain.testnet.fact_event_logs

WHERE contract_address = lower('0x30A3039675E5b5cbEA49d9a5eacbc11f9199B86D')

AND ORIGIN_FUNCTION_SIGNATURE = '0x69328dec' -- withdraw

AND '0x' || SUBSTR(PARSE_JSON(TOPICS)[1]::string, -40) = lower('0x0E4aaF1351de4c0264C5c7056Ef3777b41BD8e03') -- HONEY

AND address IS NOT NULL

),

AggregatedData AS (

SELECT

COALESCE(s.address, w.address) AS address,

COALESCE(SUM(s.amount), 0) AS total_supply,

COALESCE(SUM(w.amount), 0) AS total_withdraw,

COALESCE(SUM(s.amount), 0) - COALESCE(SUM(w.amount), 0) AS current_supply

FROM SupplyData s

FULL OUTER JOIN WithdrawData w

ON s.address = w.address

GROUP BY COALESCE(s.address, w.address)

SELECT

address,

total_supply,

total_withdraw,

current_supply