permaryupset-white
    Updated 2025-02-19
    WITH latest_ledger AS (
    SELECT MAX(sequence) as max_sequence
    FROM stellar.core.fact_ledgers
    )
    SELECT
    l.closed_at as latest_timestamp,
    COUNT(DISTINCT CASE WHEN a.deleted = FALSE THEN a.account_id END) as existing_accounts,
    COUNT(DISTINCT CASE WHEN a.deleted = TRUE THEN a.account_id END) as deleted_accounts,
    COUNT(DISTINCT a.account_id) as total_accounts
    FROM stellar.core.fact_ledgers l
    JOIN latest_ledger ll ON l.sequence = ll.max_sequence
    JOIN stellar.core.fact_accounts a ON 1=1
    GROUP BY 1;

    WITH daily_ledger AS (
    SELECT
    DATE(closed_at) AS day,
    MAX(sequence) AS max_sequence
    FROM stellar.core.fact_ledgers
    WHERE closed_at >= DATEADD(month, -12, CURRENT_DATE) -- Last 12 months
    GROUP BY 1
    )
    SELECT
    dl.day AS date,
    COUNT(DISTINCT CASE WHEN a.deleted = FALSE THEN a.account_id END) AS existing_accounts,
    COUNT(DISTINCT CASE WHEN a.deleted = TRUE THEN a.account_id END) AS deleted_accounts,
    COUNT(DISTINCT a.account_id) AS total_accounts
    FROM daily_ledger dl
    JOIN stellar.core.fact_ledgers l ON dl.max_sequence = l.sequence
    JOIN stellar.core.fact_accounts a ON 1=1
    GROUP BY 1
    ORDER BY 1;


    WITH daily_latest_ledger AS (
    SELECT
    QueryRunArchived: QueryRun has been archived