liqfees AS (

SELECT block_timestamp, DATE(block_timestamp) AS date,

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

liq_fee_rune

FROM thorchain.core.fact_swaps

--WHERE pool IN ('BTC.BTC', 'BNB.BNB', 'ETH.ETH', 'BNB.BUSD', 'ETH.USDC')

),

daily AS (

SELECT DISTINCT block_timestamp, date, dim_block_id, pool,

SUM(liq_fee_rune) OVER(PARTITION BY date, pool) AS liq_fee_rune

FROM liqfees

QUALIFY block_timestamp = MAX(block_timestamp) OVER(PARTITION BY date, pool)

),

rolling AS (

SELECT date, dim_block_id, pool,

(1/30) * SUM(liq_fee_rune) OVER(PARTITION BY pool ORDER BY date ASC ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_liquidity_fees_in_rune

FROM daily

SELECT date, block_id, pool, rolling_liquidity_fees_in_rune

FROM (rolling INNER JOIN thorchain.core.dim_block ON rolling.dim_block_id = dim_block.dim_block_id)

ORDER BY date DESC, rolling_liquidity_fees_in_rune DESC