block_timestamp::DATE AS date,

program_id,

function,

-- Classification

CASE

WHEN function = 'deposit_public' THEN 'deposit'

WHEN function IN ('instant_withdraw_public', 'withdraw_public_as_signer', 'withdraw_public') THEN 'withdraw'

ELSE 'other'

END AS transaction_type,

-- Basic extraction (simple as possible)

TRY_CAST(REGEXP_SUBSTR(inputs::STRING, 'credits:([0-9]+)', 1, 1, 'e') AS DECIMAL) / 1E6 AS input_credits_amount,

TRY_CAST(REGEXP_SUBSTR(outputs::STRING, 'credits:([0-9]+)', 1, 1, 'e') AS DECIMAL) / 1E6 AS output_credits_amount,

TRY_CAST(REGEXP_SUBSTR(inputs::STRING, '([0-9]+)u64', 1, 1, 'e') AS DECIMAL) / 1E6 AS input_u64_amount,

TRY_CAST(REGEXP_SUBSTR(outputs::STRING, '([0-9]+)u64', 1, 1, 'e') AS DECIMAL) / 1E6 AS output_u64_amount,

-- Raw data

LEFT(inputs::STRING, 100) AS inputs_preview,

LEFT(outputs::STRING, 100) AS outputs_preview

FROM aleo.core.fact_transitions

WHERE

succeeded = TRUE

AND block_timestamp >= CURRENT_DATE - 60 -- Longer time window

AND program_id LIKE '%pondo%' -- Any Pondo contract

AND (

function LIKE '%deposit%' OR

function LIKE '%withdraw%'

)

ORDER BY block_timestamp DESC