from thorchain.liquidity_actions

where lp_action = 'add_liquidity'

group by 1,2),

wd_rune as (select from_address, pool_name, sum(rune_amount) as wdrune, sum(asset_amount) as wdasset

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

group by 1,2),

currents as (select a.from_address, a.pool_name, (a.addrune-b.wdrune) as current_rune, (a.addasset-b.wdasset) as current_asset

from provide_rune a

left join wd_rune b on a.from_address = b.from_address and a.pool_name = b.pool_name),

rune as (select from_address, sum(current_rune) as total_rune, (total_rune*5) as rune_usd

from currents

group by 1

having total_rune > 0),

price as (select date_trunc('day', block_timestamp) as dt, pool_name, avg(asset_usd) as asset_usd

from thorchain.prices

where date(block_timestamp) = CURRENT_DATE - 6

group by 1,2),

asset as (select a.from_address, a.pool_name, a.current_asset, b.asset_usd, (a.current_asset*b.asset_usd) as assetusd

from currents a

left join price b on a.pool_name = b.pool_name

where a.current_asset is not null and a.current_asset > 0),

asset_total as (select from_address, sum(assetusd) as assetuusd

from asset

group by 1),

totalusd as (select a.from_address, a.rune_usd, b.assetuusd, case

when b.assetuusd is null then 0

else b.assetuusd end as asset_usd,