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