SELECT

a.ORIGIN_FUNCTION_SIGNATURE,

CAST(ethereum.public.udf_hex_to_int(REGEXP_REPLACE(SUBSTR(a.data, 11, 64), '0+$', '')) AS NUMERIC) / 1e18 AS shmon_amount

FROM monad.testnet.fact_event_logs a

WHERE a.ORIGIN_FUNCTION_SIGNATURE IN ('0x6e553f65', '0xba087652')

AND a.TX_SUCCEEDED = 'TRUE'

AND a.CONTRACT_ADDRESS = '0x3a98250f98dd388c211206983453837c8365bdc1'

AND REGEXP_REPLACE(SUBSTR(a.data, 11, 64), '0+$', '') != ''

),

totals AS (

SELECT

SUM(CASE WHEN ORIGIN_FUNCTION_SIGNATURE = '0x6e553f65' THEN shmon_amount ELSE 0 END) AS total_shmon_staked,

SUM(CASE WHEN ORIGIN_FUNCTION_SIGNATURE = '0xba087652' THEN shmon_amount ELSE 0 END) AS total_shmon_unstaked,

SUM(CASE WHEN ORIGIN_FUNCTION_SIGNATURE = '0x6e553f65' THEN shmon_amount ELSE 0 END) -

SUM(CASE WHEN ORIGIN_FUNCTION_SIGNATURE = '0xba087652' THEN shmon_amount ELSE 0 END) AS net_shmon_flow

FROM combined

SELECT

'Minted(Stakes)' AS flow_type,

total_shmon_staked AS shmon_amount

FROM totals

UNION ALL

SELECT

'Burned(Unstakes)' AS flow_type,

total_shmon_unstaked AS shmon_amount

FROM totals

UNION ALL

SELECT

'Net Flow' AS flow_type,

net_shmon_flow AS shmon_amount

FROM totals;