SELECT block_timestamp,

CASE

WHEN tx_id is not null then tx_id

ELSE asset_tx_id

END as tx_id,

CASE

WHEN from_address is not null THEN from_address

ELSE asset_address

END as address,

pool_name, stake_units, asset_amount, rune_amount, 1 as type

FROM thorchain.defi.fact_liquidity_actions

WHERE lp_action = 'add_liquidity' and pool_name like '%.%'

),

withdraw_units AS (

SELECT block_timestamp,

CASE

WHEN tx_id is not null then tx_id

ELSE asset_tx_id

END as tx_id,

CASE

WHEN from_address is not null THEN from_address

ELSE asset_address

END as address, pool_name, -rune_amount as rune_amount, -asset_amount as asset_amount, -stake_units as stake_units, 0 as type

FROM thorchain.defi.fact_liquidity_actions WHERE lp_action = 'remove_liquidity' and pool_name like '%.%'

),

unioned_units AS (

SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, rune_amount, type FROM deposit_units

UNION

SELECT block_timestamp, tx_id, address, pool_name, stake_units, asset_amount, rune_amount, type FROM withdraw_units

),

pool_depths AS (SELECT day,

asset as pool_name

FROM thorchain.defi.fact_pool_block_statistics

WHERE (pool_name LIKE '%.%')),