Updated 2025-02-19
    WITH stablecoin_data AS (
    SELECT
    SELLING_ASSET_CODE AS token,
    SUM(SELLING_AMOUNT) / 1e7 AS stablecoin_market_cap -- Correction pour Stellar stroops
    FROM stellar.defi.fact_trades
    WHERE SELLING_ASSET_CODE IN ('USDC', 'EURC', 'GOLD', 'TOKENIZED_BOND')
    GROUP BY SELLING_ASSET_CODE
    ),
    tvl_data AS (
    SELECT
    DATE_TRUNC('day', LEDGER_CLOSED_AT) AS date,
    SUM(SELLING_AMOUNT) / 1e7 AS total_value_locked -- Correction de l'échelle
    FROM stellar.defi.fact_trades
    WHERE LEDGER_CLOSED_AT >= DATEADD(DAY, -7, CURRENT_DATE)
    GROUP BY DATE_TRUNC('day', LEDGER_CLOSED_AT)
    ),
    volume_data AS (
    SELECT
    SUM(SELLING_AMOUNT) / 1e7 AS total_volume_24h -- Correction de l'échelle
    FROM stellar.defi.fact_trades
    WHERE LEDGER_CLOSED_AT >= DATEADD(HOUR, -24, CURRENT_DATE)
    ),
    xlm_data AS (
    SELECT
    AVG(CAST(PRICE_N AS FLOAT) / CAST(PRICE_D AS FLOAT)) AS xlm_price -- Correction du prix XLM
    FROM stellar.defi.fact_trades
    WHERE SELLING_ASSET_CODE = 'XLM'
    AND LEDGER_CLOSED_AT >= DATEADD(DAY, -7, CURRENT_DATE)
    )
    SELECT
    (SELECT SUM(stablecoin_market_cap) FROM stablecoin_data) AS stablecoins_market_cap,
    (SELECT SUM(total_value_locked) FROM tvl_data) AS tvl_total,
    (SELECT total_volume_24h FROM volume_data) AS total_volume_24h,
    (SELECT xlm_price FROM xlm_data) AS xlm_price;

    Last run: 26 days ago
    STABLECOINS_MARKET_CAP
    TVL_TOTAL
    TOTAL_VOLUME_24H
    XLM_PRICE
    1
    4908127.5690089821341959.52270063831087.27488765244.249168784
    1
    68B
    5s