DATE_TRUNC('month', DAY) AS period,

POOL_NAME,

--TOTAL ADD + REMOVE LIQUIDITY

SUM(ADD_ASSET_LIQUIDITY_USD) + SUM(ADD_RUNE_LIQUIDITY_USD)

+ SUM(WITHDRAW_ASSET_LIQUIDITY_USD) + SUM(WITHDRAW_RUNE_LIQUIDITY_USD)

AS sum_add_and_rem_liq,

--AVERAGE POOL SIZE ON THE MONTH

AVG(ASSET_LIQUIDITY * ASSET_PRICE_USD + RUNE_LIQUIDITY * RUNE_PRICE_USD)

AS avg_pool_size,

-- TURNOVER RATIO ie % OF THE LIQUIDITY ADDED OR REMOVED

100*sum_add_and_rem_liq/avg_pool_size

AS liq_turnover,

FROM thorchain.defi.fact_daily_pool_stats

WHERE DAY :: DATE >= '2023-01-01'

AND POOL_NAME IN ('BTC.BTC')

GROUP BY period , POOL_NAME;

ORDER BY period DESC ;