10BlockchainTVL essai
    Updated 2025-02-19
    WITH aquarius_tokens AS (
    /* Liste des 13 paires (CODE, ISSUER) qu'on a en intersection
    Remplace l'exemple ci-dessous par tes vraies 13 paires
    */
    SELECT 'USDC' AS code, 'GA5ZSEJYB37JRC5AVCIA5MOP4RHTM335X2KGX3IHOJAPP5RE34K4KZVN' AS issuer
    UNION ALL SELECT 'USDC', 'GCBYVQH3RZ4JDVFMNWETE3J6U3AW6NNGTIWNVJHNQIIEGQR4K7PLUSDC'
    UNION ALL SELECT 'EURC', 'GAP2JFYUBSSY65FIFUN3NTUKP6MQQ52QETQEBDM25PFMQE2EEN2EEURC'
    UNION ALL SELECT 'EURC', 'GDHU6WRG4IEQXM5NZ4BMPKOXHW76MZM4Y2IEMFDVXBSDP6SJY4ITNPP2'
    UNION ALL SELECT 'USDGLO','GBBS25EGYQPGEZCGCFBKG4OAGFXU6DSOQBGTHELLJT3HZXZJ34HWS6XV'
    UNION ALL SELECT 'CAPI','GAU5GYDA3COPNP5GNZAVCZLHAH6I74645TIMPR7SPICYERRJHS6K5VED'
    UNION ALL SELECT 'STROOPY','GBZ3SMMNNSOLVV6LGNDBDWRVD2N56VUZZ6LVB6VGOOJGSMWI7ISLOJEC'
    UNION ALL SELECT 'USDP','GDTEQF6YGCKLIBD37RJZE5GTL3ZY6CBQIKH7COAW654SYEBE6XJJOLOW'
    UNION ALL SELECT 'SAVE','GBWKWJTPYLDEIUZ3EZ34HGXWRQ4R6DUCNLG5SIT72RL243IJZLLG5UOJ'
    UNION ALL SELECT 'AQUA','GBNZILSTVQZ4R7IKQDGHYGY2QXL5QOFJYQMXPKWRRM5PAV7Y4M67AQUA'
    UNION ALL SELECT 'yETH','GDYQNEF2UWTK4L6HITMT53MZ6F5QWO3Q4UVE6SCGC4OMEQIZQQDERQFD'
    UNION ALL SELECT 'FOOD','GBYYKQYUY2XWVMM4VFCBJJVCCQC3QJ3BCLV4276DWHY4BQ7V5AB4U5VA'
    UNION ALL SELECT 'yUSDC','GDGTVWSM4MGS4T7Z6W4RPWOCHE2I6RDFCIFZGS3DOA63LWQTRNZNTTFF'
    ),

    daily_pool_snapshots AS (
    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
    ),