Problems:

• For some addresses I only found withdrawals, not liquidity additions in the table, whereas the additions could be found with thorchain's explorer. Example:'qpwwjxryc5r2euagwcm669k6m4l08mafqugquyjh4q'

Methods

1. Get all Lq_provider addresses with non-negative cumulative staking_units for each pool, each day. (The active accounts at a given time).

2. For each active account, each day, find the first consecutive previous day (in this provision period) the account was active.

3. For each active account, each day, calculate the staking-weight-averaged time of holding (up to the current day). See explanation below how to calculate staking-weight-averaged time of holding.

4. For each pool, each day, calculate the weight-averaged active holding time by averaging the holding time of all active accounts weighted with their current cumulative stake

5. For each pool, each day, multiply the weight-averaged active holding time for the pool with amount of runes in the pool (the pools relative weight compared to other pools)
   Alternatively, the multiply with the amount of total liquidity (in USD) in the pool

6. Plot time series of weighted holding-time distribution between pools

7. Calculate and plot time series of weight-averaged active holding time over the entire network.

Calculating the staking-weight-averaged time for a given account:

Imagine that each active coin, or liquidity staking unit in a pool 'earns' time while it's active and stops earning once it's removed. In order get the average time in the pool per coin we need to divide the total coin-time accumulated by the account with the sum of coins doing the accumulation. In this sense, every added coin is new, we need to sum the additions in the last staking 'run' of the account. We can get the total coin-time up to a given time doing a 'horizontal integration': Multiply each addition or subtraction (use -) of liquidity with the time remaining and sum this for the account.

SQL shenanigans

Finding the active accounts each day:

Find the date for each, date each address of the last transaction. Filtering a cross join of all transactions with the dates to this for each address, get the cumulative sum of pool tokens at the last transaction. Active accounts will be those with a larger than 0 cumulative pool token count at the last transaction.

Added a 0 to the cumulative lp-token-sum of every address at date 2000-01-01 to ensure that every staking run has a preceding transaction.

What is the liquidity weighted stake-hold duration on the THOR network?
This duration is significant, because it's increase signals the continued trust of liquidity providers in the network and the value of the underlying assets in the pools. On the flipside, the 1/duration at steady state is the proportion of new capital required to enter each day to keep the duration stable.

Getting this value requires calculating the average active holding time of each pool, which in turn requires calculating the average holding time of each active account in the pool in it's latest staking 'run'. Steps to derive these values are detailed in the Methods section.

Interpretation: The growth of the average staking duration means that the amount of liquidity entering the pools is less than the liquidity already in the pool divided by it's current age.
Put in other terms, the 1/Duration*100 at steady state is the percentage of new liquidity joining the pools in proportion to the already present liquidity. Thorchain stabilizing between 100 and 150 days of LP duration shows an average of 1 - 0.6% liquidity inflow rate.
A drop in the average age shows a significant amount of fresh deposits or the removal of old deposits in an agewise non-homogeneous pool. Conversely, a larger than 1 day/day slope is only possible if a recent addition to a pool is removed and the pools average age jumps back up.
Last, a near 1 day/day slope is only possible with no significant new influx of capital.

History review:

• During the first 3 month of trackable data the Average Staking Duration across Thorchain (ASDaT) increased to 30 days in a choppy fashion ie. 10 days/month on average. This was the goldrush era for Thorchain; the rate of daily new capital inflow was ~2-10%
• Between July.3 and October.20 the ASDaT grew from 30 days to 130 days in 109 days (27 days/month). Capital inflows went dead for 3 months. The start of this coincides with the Thorchain hack in July21
• From Oct.20 to Nov.3 ASDaT fell from 130 to 80 (-50 in 14 days), and -34 days in a single day between Nov.2 to Nov.3. short burst of inflow
• Following Nov.3 ASDaT started rising again reaching 138 days by Dec. 21 (58 days in 48 days) Another lull in staking activity with some of the new significant stakes withdrawn between Dec.8 and Dec.15
• From Dec.21 to Jan.31 ASDaT remained stable around 130 with a slightly decreasing trend (-10 days in 40 days) stable activity period with 0.75% inflow rate
• resumed a lower rate of increase from Febr.1 to March 3, reaching 150 days in a month(23 days/month rate). close to inactive period
• The ASDaT falls after March up to Apr. 6 falling to 101 days (-49 days in 34 days) and -25 days in just 3 days between March 24 to March 27.
• From March 27 to the present the average staking duration remained stable between 100 and 107 days, recently trending upwards. stable staking period with just below 1% inflow rate

The second graph shows the distribution of weighted holding times among the pools over time and their relative importance in setting the average duration.

• BTC, BNB and ETH remained the stable determining pools, with their cumulative weights making up 40-60% of the total.
• SUSHI rose to a brief period of prominence with 10% weight in May21, but faded out by the end of July
• USDT was the dominant stablecoin at the start, but was taken over by USDC and especially BUSD after July21
• ETH.XRUNE rose to ~15% after July21, but had to gradually share it's weight with ETH.THOR after Nov21
• UST and LUNA was introduced in Apr22 and seem to be gaining weight. Currently standing ~5%

Use the legend to select individual average active staking duration traces of the pools

Examining the mean holding time each pool individually:

• BTC, ETH, BUSD: Very similar to the average shape, but BTC, lacks most of the period of increase in Febr22 and its average staking duration was stable between 2021.Dec.21 and 2022.March.03 around 150 days of average staking duration.
• BNB lacks the period of stable investment in January22 and shows continued aging from a Nov21 to March22
• TERRA.UST and LUNA: continued aging since introduction in March22, with an ~ 10-15% increase to Terra.UST on Apr.14