SELECT block_timestamp, asset_tx_id as tx_id, asset_address as address, pool_name, stake_units, asset_e8 / pow(10, 8) as amount_change, 1 as type

FROM thorchain.defi.fact_stake_events

WHERE pool_name LIKE '%/%'

),

withdraw_units AS (

SELECT block_timestamp, tx_id, from_address as address, pool_name, -stake_units as stake_units, 0 as amount_change, basis_points, 0 as type

FROM thorchain.defi.fact_withdraw_events

WHERE (MEMO like '%-:%' or MEMO like '%WITHDRAW%' or MEMO like '%wd%') and MEMO like '%/%' and block_timestamp is not null

),

unioned_units AS (

SELECT block_timestamp, tx_id, address, pool_name, stake_units, amount_change, basis_points, type FROM withdraw_units

UNION

SELECT block_timestamp, tx_id, address, pool_name, stake_units, amount_change, 0, type FROM deposit_units

),

merge_same_time AS (SELECT block_timestamp, MAX(tx_id) as tx_id, address, pool_name, SUM(stake_units) as stake_units, SUM(amount_change) as amount_change,

1-(CASE WHEN MIN(ABS(1-basis_points/10000)) = 0

THEN 0 ELSE EXP(SUM(LN(ABS(NULLIF(1-basis_points/10000,0))))) END) as basis_points, MAX(type) as type FROM unioned_units GROUP BY block_timestamp, address, pool_name),

corrected AS (SELECT block_timestamp, tx_id, address, pool_name, stake_units,

CASE

WHEN type = 1 then amount_change

ELSE basis_points * 10000

END AS value, type FROM merge_same_time),

units_full AS (SELECT *,

SUM(stake_units) OVER(PARTITION BY address, pool_name ORDER BY block_timestamp) AS user_units,

LEAD(block_timestamp, 1, CURRENT_TIMESTAMP) OVER(PARTITION BY address, pool_name ORDER BY block_timestamp) as next_timestamp

from corrected),

pre_process_data AS (SELECT *,

iff(type = 0, 0, value * POW(10, 8))::number AS add,

iff(type = 0, value, 0)::number AS per,

row_number()over(PARTITION BY address, pool_name ORDER BY block_timestamp) AS rn

FROM units_full