tab1 as (

SELECT

trunc(block_timestamp,'day') as days,

split(memo, ':')[1]::string as operator,

case when memo like '%BOND%' then split(memo, ':')[2]::string

else split(memo, ':')[3]::string end as bonder,

upper(split(memo, ':')[0]::string) AS action,

count(distinct tx_id) as participations,

--cum(bonds) over (partition by operator order by days) as cum_bonds,

sum(e8/pow(10,8)) as rune_to_bond

--sum(bonded) over (partition by operator order by days) as cum_rune_to_unbond

FROM thorchain.defi.fact_bond_events

WHERE memo like '%BOND:%' or memo like '%UNBOND:%'

group by 1,2,3,4

ORDER BY 1

),

tab2 as (

SELECT

days,

operator,

greatest(0, sum(CASE WHEN bonder IS NULL AND action = 'BOND' THEN rune_to_bond

WHEN bonder IS NULL AND action IN ('UNBOND', 'LEAVE') THEN -rune_to_bond ELSE 0 END)) AS bond_operator,

greatest(0, sum(CASE WHEN bonder IS NOT NULL AND action = 'BOND' THEN rune_to_bond

WHEN bonder IS NOT NULL AND action = 'UNBOND' THEN -rune_to_bond ELSE 0 END)) AS bond_bonder

FROM tab1

GROUP BY 1,2

),

tab3 as (

select

distinct operator,

sum(bond_operator) as total_bond_operator,

sum(bond_bonder) as total_bond_bonder

from tab2

group by 1

),