with add as (select from_address as address, pool_name as poolname, sum(stake_units) as units from thorchain.liquidity_actions where lp_action = 'add_liquidity' group by 1,2), remove as (select from_address as address, pool_name as poolname, sum(stake_units) as units from thorchain.liquidity_actions where lp_action = 'remove_liquidity' group by 1,2), combo as (select a.address as addresses, a.poolname as pools, a.units as add_units, b.units as remove_units, a.units - ifnull(b.units, 0) as net from add a left join remove b on (a.address = b.address and a.poolname = b.poolname) --where addresses = 'thor1tecpk5zmd225ec4ma56q20j0xejd7ay7xmtajl' where net > 0 group by 1,2,3,4,5), table1 as (select addresses, a.pools, date_trunc('day',block_timestamp) as day from combo a join thorchain.liquidity_actions b on a.addresses = b.from_address and a.pools = b.pool_name where lp_action = 'add_liquidity'), table2 as (select addresses, pools, min(day) as earliest_add from table1 group by 1,2) select addresses, --ifnull(count(addresses),0) as addresses, pools, split(pools, '-')[0] as pool_namez, datediff(day, earliest_add, current_date) as lp_period /* case when datediff(day, earliest_add, current_date) <= 30 then '< 1 month' when datediff(day, earliest_add, current_date) > 30 and datediff(day, earliest_add, current_date) <= 60 then '2 months' when datediff(day, earliest_add, current_date) > 60 and datediff(day, earliest_add, current_date) <= 90 then '3 months' when datediff(day, earliest_add, current_date) > 90 and datediff(day, earliest_add, current_date) <= 120 then '4 months' when datediff(day, earliest_add, current_date) > 120 and datediff(day, earliest_add, current_date) <= 150 then '5 months' when datediff(day, earliest_add, current_date) > 150 and datediff(day, earliest_add, current_date) <= 180 then '6 months' when datediff(day, earliest_add, current_date) > 180 and datediff(day, earliest_add, current_date) <= 210 then '7 months' when datediff(day, earliest_add, current_date) > 210 and datediff(day, earliest_add, current_date) <= 240 then '8 months' when datediff(day, earliest_add, current_date) > 240 and datediff(day, earliest_add, current_date) <= 270 then '9 months' when datediff(day, earliest_add, current_date) > 270 and datediff(day, earliest_add, current_date) <= 300 then '10 months' else '> 10 months' END AS groups, case when datediff(day, earliest_add, current_date) <= 30 then 1 when datediff(day, earliest_add, current_date) > 30 and datediff(day, earliest_add, current_date) <= 60 then 2 when datediff(day, earliest_add, current_date) > 60 and datediff(day, earliest_add, current_date) <= 90 then 3 when datediff(day, earliest_add, current_date) > 90 and datediff(day, earliest_add, current_date) <= 120 then 4 when datediff(day, earliest_add, current_date) > 120 and datediff(day, earliest_add, current_date) <= 150 then 6 when datediff(day, earliest_add, current_date) > 150 and datediff(day, earliest_add, current_date) <= 180 then 7 when datediff(day, earliest_add, current_date) > 180 and datediff(day, earliest_add, current_date) <= 210 then 8 when datediff(day, earliest_add, current_date) > 210 and datediff(day, earliest_add, current_date) <= 240 then 9 when datediff(day, earliest_add, current_date) > 240 and datediff(day, earliest_add, current_date) <= 270 then 10 when datediff(day, earliest_add, current_date) > 270 and datediff(day, earliest_add, current_date) <= 300 then 11 else 12 end as ordering*/ from table2 order by 3 asc

Weighted-Average LP Duration