MONTH | EXISTING_ACCOUNTS | DELETED_ACCOUNTS | TOTAL_ACCOUNTS | |
|---|---|---|---|---|
1 | 2025-02-01 00:00:00.000 | 194 | 0 | 194 |
2 | 2025-01-01 00:00:00.000 | 194 | 1 | 195 |
3 | 2024-12-01 00:00:00.000 | 358 | 0 | 358 |
4 | 2024-11-01 00:00:00.000 | 277 | 0 | 277 |
5 | 2024-10-01 00:00:00.000 | 192 | 0 | 192 |
6 | 2024-09-01 00:00:00.000 | 232 | 0 | 232 |
7 | 2024-08-01 00:00:00.000 | 287 | 0 | 287 |
8 | 2024-07-01 00:00:00.000 | 249 | 1 | 250 |
9 | 2024-06-01 00:00:00.000 | 161 | 0 | 161 |
10 | 2024-05-01 00:00:00.000 | 178 | 0 | 178 |
11 | 2024-04-01 00:00:00.000 | 352 | 0 | 352 |
12 | 2024-03-01 00:00:00.000 | 250 | 0 | 250 |
13 | 2024-02-01 00:00:00.000 | 484 | 0 | 484 |
permaryupset-white
Updated 2025-02-19
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
Last run: 2 months ago
13
495B
3s