WITH

weekly_rune_liqfees AS (

SELECT DATE_TRUNC('week', block_timestamp) AS date,

SPLIT(pool_name, '-')[0] AS pool,

SUM(liq_fee_in_rune_e8) / 1e8 AS weekly_rune_liquidity_fees

FROM thorchain.core.fact_swaps_events

WHERE block_timestamp IS NOT NULL

AND pool_name = 'BTC.BTC' --To make chart display easier

GROUP BY date, pool

),

closing_rune_depth AS (

SELECT DATE_TRUNC('week', block_timestamp) AS date,

SPLIT(pool_name, '-')[0] AS pool,

rune_e8 / 1e8 AS rune_depth

FROM (

SELECT DISTINCT block_timestamp, pool_name, rune_e8

FROM thorchain.core.fact_block_pool_depths

WHERE block_timestamp IS NOT NULL

and rune_e8 > 0

QUALIFY block_timestamp = MAX(block_timestamp) OVER(PARTITION BY pool_name, DATE_TRUNC('week', block_timestamp))

)

SELECT weekly_rune_liqfees.date, weekly_rune_liqfees.pool,

weekly_rune_liquidity_fees, rune_depth,

weekly_rune_liquidity_fees / NULLIF(rune_depth, 0) AS fees_per_depth,

rune_depth / NULLIF(weekly_rune_liquidity_fees, 0) AS depth_per_fees

FROM (weekly_rune_liqfees INNER JOIN closing_rune_depth

ON weekly_rune_liqfees.date = closing_rune_depth.date

AND weekly_rune_liqfees.pool = closing_rune_depth.pool)

ORDER BY date DESC, rune_depth DESC