permaryAsset Transfer Volume
    Updated 2025-02-18
    WITH asset_transfers AS (
    SELECT
    DATE(CLOSED_AT) AS date,
    COALESCE(ASSET_CODE, 'XLM') AS asset_code,
    ASSET_ISSUER,
    SUM(AMOUNT) / 10000000 AS total_transferred, -- Convert to standard units
    COUNT(*) AS transfer_count,
    l.label AS issuer_label
    FROM stellar.core.fact_operations f
    LEFT JOIN stellar.core.dim_labels l
    ON f.asset_issuer = l.address
    WHERE
    TYPE_STRING = 'payment'
    AND CLOSED_AT >= DATEADD('day', -30, CURRENT_DATE())
    GROUP BY
    date,
    asset_code,
    ASSET_ISSUER,
    l.label
    ),
    asset_totals AS (
    SELECT
    asset_code,
    COALESCE(issuer_label, asset_issuer) AS issuer,
    SUM(total_transferred) AS total_volume,
    SUM(transfer_count) AS total_transactions
    FROM asset_transfers
    GROUP BY
    asset_code,
    COALESCE(issuer_label, asset_issuer)
    )

    SELECT
    asset_code,
    issuer,
    ROUND(total_volume, 2) AS total_volume, -- Rounded to 2 decimal places
    QueryRunArchived: QueryRun has been archived