WITH earnings AS (

SELECT

DAY,

ASSET_LIQUIDITY * ASSET_PRICE_USD AS liq_asset_USD,

RUNE_LIQUIDITY * RUNE_PRICE_USD AS liq_rune_USD,

SYSTEM_REWARDS_USD + TOTAL_SWAP_FEES_USD AS tot_earnings_USD,

100 * ((SYSTEM_REWARDS_USD + TOTAL_SWAP_FEES_USD) / (liq_rune_USD + liq_asset_USD)) AS earnings_to_liq

FROM

thorchain.defi.fact_daily_pool_stats

WHERE

POOL_NAME = 'ETH.WBTC-0X2260FAC5E5542A773AA44FBCFEDF7C193BC2C599'

AND DAY > '2023-01-01'

),

smooth_earnings AS (

SELECT

DAY,

earnings_to_liq,

SUM(earnings_to_liq) OVER (ORDER BY DAY ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS smooth_earnings_to_liq,

COUNT(*) OVER (ORDER BY DAY ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS RollingDayCount

FROM

earnings

),

yearly_yield AS (

SELECT

DAY,

smooth_earnings_to_liq,

RollingDayCount,

(smooth_earnings_to_liq * (365 / NULLIF(RollingDayCount, 0))) AS APR

FROM

smooth_earnings