SELECT DISTINCT

DAY,

EXTRACT (YEAR FROM DAY) AS YEAR,

EXTRACT (WEEK FROM DAY) AS WEEK,

FIRST_VALUE(DAY) OVER (PARTITION BY ASSET, YEAR, WEEK ORDER BY DAY DESC) AS LAST_DAY_OF_WEEK,

ASSET AS POOL,

LIQUIDITY_UNIT_VALUE_INDEX AS LUVI_DAY,

AVG(LUVI_DAY)

OVER (PARTITION BY ASSET, YEAR, WEEK ORDER BY DAY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS LUVI_WEEK,

AVG(LUVI_DAY)

OVER (PARTITION BY ASSET ORDER BY DAY ASC ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)

AS LUVI_7D,

RUNE_DEPTH / ASSET_DEPTH AS RUNE_PER_BTC_DAY,

AVG(RUNE_PER_BTC_DAY)

OVER (PARTITION BY ASSET, YEAR, WEEK ORDER BY DAY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS RUNE_PER_BTC_WEEK,

AVG(RUNE_PER_BTC_DAY)

OVER (PARTITION BY ASSET ORDER BY DAY ASC ROWS BETWEEN 7 PRECEDING AND CURRENT ROW)

AS RUNE_PER_BTC_7D,

RUNE_PER_BTC_DAY - COALESCE(LAG(RUNE_PER_BTC_DAY, 1) OVER (PARTITION BY ASSET ORDER BY DAY), 0)

AS DELTA_RUNE_PER_BTC_DAY

FROM thorchain.defi.fact_pool_block_statistics

WHERE ASSET = 'BTC.BTC'

AND DAY >= '{{start_date}}'::DATE - INTERVAL '1 month'

AND DAY <= '{{end_date}}'::DATE

ORDER BY DAY

),

prime AS (

SELECT

*,

AVG(DELTA_RUNE_PER_BTC_DAY)

OVER (PARTITION BY POOL, YEAR, WEEK ORDER BY DAY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AS DELTA_RUNE_PER_BTC_WEEK,

AVG(DELTA_RUNE_PER_BTC_DAY)