SELECT

LIQUIDITY_POOL_ID,

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day_ts,

ASSET_A_CODE,

ASSET_A_ISSUER,

ASSET_A_AMOUNT,

ASSET_B_CODE,

ASSET_B_ISSUER,

ASSET_B_AMOUNT,

ROW_NUMBER() OVER (

PARTITION BY LIQUIDITY_POOL_ID, DATE_TRUNC('day', BLOCK_TIMESTAMP)

ORDER BY BLOCK_TIMESTAMP DESC

) AS rn

FROM stellar.defi.fact_liquidity_pools

WHERE DELETED = FALSE -- On ignore la colonne TYPE, qui est vide

),

last_snapshots AS (

-- On ne retient que la dernière ligne du jour pour chaque pool

SELECT *

FROM daily_pool_snapshots

WHERE rn = 1

),

price_data AS (

SELECT

DATE_TRUNC('day', HOUR) AS day_ts,

ASSET_CODE,

ASSET_ISSUER,

AVG(PRICE) AS daily_price

FROM stellar.price.ez_prices_hourly

GROUP BY 1, 2, 3

),

pool_valued AS (