WITH t0 AS (

SELECT pool_name

, date_trunc('month', block_timestamp) AS month

, COUNT(DISTINCT block_timestamp::date) AS n_days

, SUM(CASE WHEN lp_action = 'add_liquidity' THEN stake_units ELSE -stake_units END) AS net_stake_units

FROM thorchain.core.fact_liquidity_actions

GROUP BY 1, 2

), t1 AS (

SELECT *

, SUM(net_stake_units) OVER (PARTITION BY pool_name ORDER BY month) AS cumu_stake_units

FROM t0

), t2 AS (

SELECT *

, date_trunc('month', block_timestamp) AS month

, ROW_NUMBER() OVER (PARTITION BY pool_name, month ORDER BY block_timestamp DESC) AS rn

FROM thorchain.core.fact_pool_block_balances

), t3 AS (

SELECT t2.*

, t1.n_days

, cumu_stake_units * (synth_amount * POW(10, 8)) / (((asset_amount * 2) - synth_amount) * POW(10, 8)) AS synth_units

, POW((asset_amount * POW(10, 8)) * (rune_amount * POW(10, 8)), 0.5) / (synth_units + cumu_stake_units) AS luvi

, ROW_NUMBER() OVER (PARTITION BY t2.pool_name ORDER BY t2.month DESC) AS rn2

FROM t2

JOIN t1 ON t1.pool_name = t2.pool_name AND t1.month = t2.month

WHERE rn = 1

AND ((asset_amount * 2) - synth_amount) > 0

AND (synth_units + cumu_stake_units) > 0

), t4 AS (

SELECT b.*

, p.luvi AS p_luvi

, (b.luvi / p.luvi) - 1 AS luvi_pct_chg

, b.rune_amount * luvi_pct_chg AS w_luvi_pct_chg

FROM t3 b

JOIN t3 p ON p.pool_name = b.pool_name AND p.rn2 = b.rn2 + 1

WHERE p.luvi > 0