permaryupset-white
Updated 2025-02-19Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
⌄
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