WITH terra_lplers AS (

SELECT from_address AS address, sum(rune_amount) AS removed_rune_amount, min(block_timestamp) AS first_removal_time

FROM flipside_prod_db.thorchain.liquidity_actions

WHERE (pool_name = 'TERRA.LUNA' OR pool_name = 'TERRA.UST') AND

lp_action = 'remove_liquidity' AND

block_timestamp > TO_DATE('2022-05-15')

GROUP BY address

HAVING sum(rune_amount) > 0

), lp_addtions AS (

SELECT from_address, sum(rune_amount) AS added_rune_amount

FROM flipside_prod_db.thorchain.liquidity_actions

JOIN terra_lplers ON address = from_address AND block_timestamp > first_removal_time

WHERE lp_action = 'add_liquidity'

GROUP BY from_address

),

swaps AS (

SELECT from_address, sum(from_amount) AS swapped_rune_amount

FROM flipside_prod_db.thorchain.swaps

JOIN terra_lplers ON address = from_address AND block_timestamp > first_removal_time

WHERE from_asset = 'THOR.RUNE'

GROUP BY from_address

),

swappers_and_lplers AS (

SELECT DISTINCT address

FROM terra_lplers

LEFT JOIN lp_addtions ON lp_addtions.from_address = address

LEFT JOIN swaps ON swaps.from_address = address

WHERE added_rune_amount > 0 OR swapped_rune_amount > 0

),

tranfers AS (

SELECT t.from_address, SUM(rune_amount) AS transferred_rune_amount

FROM flipside_prod_db.thorchain.transfers t

JOIN terra_lplers ON address = from_address AND block_timestamp > first_removal_time

LEFT JOIN crosschain.address_labels l ON l.address = t.to_address AND blockchain = 'thorchain'

WHERE asset = 'THOR.RUNE' AND COALESCE(l.label_type, '') NOT IN ('defi','dex')