DATE_TRUNC('week', DAY) AS WEEK,

POOL_NAME,

SUM(SWAP_VOLUME_RUNE_USD) AS tot_swap_volume_rune_usd,

AVG(RUNE_PRICE_USD) AS avg_rune_price_usd,

AVG(RUNE_LIQUIDITY) AS avg_rune_liq,

AVG(ASSET_LIQUIDITY) AS avg_asset_liq,

AVG(ASSET_PRICE_USD) AS avg_asset_price_usd,

CASE

WHEN SUM(SWAP_VOLUME_RUNE_USD) > 0.0

THEN

100*(SUM(SWAP_VOLUME_RUNE_USD)/(AVG(RUNE_PRICE_USD) * AVG(RUNE_LIQUIDITY)+ AVG(ASSET_PRICE_USD) * AVG(ASSET_LIQUIDITY)))

ELSE 0.0

END

AS volume_depth

FROM thorchain.defi.fact_daily_pool_stats

WHERE DAY >= '2023-01-01'

AND POOL_NAME IN ('BTC.BTC')

GROUP BY WEEK , POOL_NAME

ORDER BY WEEK DESC;