WITH LQ_SUMS1 AS (

SELECT block_timestamp, LP_action, pool_name, from_address as lq_provider_address,

CASE

WHEN LP_action = 'add_liquidity' THEN stake_units

END AS pool_addition,

CASE

WHEN LP_action = 'add_liquidity' THEN stake_units

WHEN LP_action = 'remove_liquidity' THEN -stake_units

END AS pool_lq_change

FROM thorchain.liquidity_actions

),

LQ_CUMSUM AS (

SELECT block_timestamp, LP_action, pool_name, lq_provider_address, pool_addition, pool_lq_change,

sum(pool_lq_change) OVER (PARTITION BY pool_name, lq_provider_address ORDER BY block_timestamp ROWS BETWEEN unbounded preceding and CURRENT ROW) AS pool_cumsum

FROM LQ_SUMS1

),

DATE_POOL AS (

SELECT date_trunc('day',block_timestamp) as dt, pool_name AS active_pool

FROM LQ_CUMSUM

GROUP BY 1,2

),

CROSS_TABLE1 AS (

SELECT *

FROM DATE_POOL AS D CROSS JOIN LQ_CUMSUM AS C

WHERE dateadd(day,1,D.dt) > C.block_timestamp AND

D.active_pool = C.pool_name

),

GETLAST AS (

SELECT dt, pool_name, lq_provider_address, max(block_timestamp) AS time_of_last_chng

FROM CROSS_TABLE1

GROUP BY dt, pool_name, lq_provider_address

),

FILT2ACTIVE AS (