SELECT

DATE_TRUNC('day', block_timestamp) as date,

CASE

WHEN function LIKE '%add_liquidity%' THEN 'ADD'

WHEN function LIKE '%remove_liquidity%' THEN 'REMOVE'

END as action,

SPLIT(GET(inputs[3], 'value'), 'u')[0]::number/POW(10,6) as amount,

GET(inputs[0], 'value') as pool_id

FROM aleo.core.fact_transitions

WHERE

program_id = 'arcn_pool_v2_2_2.aleo'

AND succeeded = TRUE

AND (function LIKE '%add_liquidity%' OR function LIKE '%remove_liquidity%')

),

daily_flows AS (

SELECT

date,

COUNT(DISTINCT pool_id) as active_pools,

SUM(CASE

WHEN action = 'ADD' THEN amount

WHEN action = 'REMOVE' THEN -amount

ELSE 0

END) as net_daily_flow

FROM pool_transactions

GROUP BY date

),

running_totals AS (

SELECT

date,

active_pools,

net_daily_flow,

SUM(net_daily_flow) OVER (ORDER BY date) as cumulative_tvl_aleo

FROM daily_flows

SELECT