WITH

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 rune_liquidity_fees

FROM thorchain.core.fact_swaps_events

WHERE block_timestamp IS NOT NULL

AND SPLIT(pool_name, '-')[0] IN

('BTC.BTC', 'ETH.ETH', 'BNB.BNB',

'AVAX.AVAX', 'BCH.BCH', 'DOGE.DOGE', 'GAIA.ATOM', 'LTC.LTC',

'BNB.BUSD', 'ETH.USDC')

--Selected pools

AND SPLIT(memo, ':')[4] <> ''

--Comment out this line if wanting to see fees from no-affiliate-specified swaps too.

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 rune_liqfees.date, rune_liqfees.pool,

rune_liquidity_fees, rune_depth,

rune_liquidity_fees / NULLIF(rune_depth, 0) AS fees_per_depth,

rune_depth / NULLIF(rune_liquidity_fees, 0) AS depth_per_fees