DAY | RICH_COUNT | |
|---|---|---|
1 | 2021-04-10 00:00:00.000 | 2 |
2 | 2021-04-11 00:00:00.000 | 6 |
3 | 2021-04-12 00:00:00.000 | 13 |
4 | 2021-04-13 00:00:00.000 | 30 |
5 | 2021-04-14 00:00:00.000 | 42 |
6 | 2021-04-15 00:00:00.000 | 63 |
7 | 2021-04-16 00:00:00.000 | 74 |
8 | 2021-04-17 00:00:00.000 | 80 |
9 | 2021-04-18 00:00:00.000 | 98 |
10 | 2021-04-19 00:00:00.000 | 108 |
11 | 2021-04-20 00:00:00.000 | 116 |
12 | 2021-04-21 00:00:00.000 | 122 |
13 | 2021-04-22 00:00:00.000 | 137 |
14 | 2021-04-23 00:00:00.000 | 145 |
15 | 2021-04-24 00:00:00.000 | 152 |
16 | 2021-04-25 00:00:00.000 | 159 |
17 | 2021-04-26 00:00:00.000 | 171 |
18 | 2021-04-27 00:00:00.000 | 176 |
19 | 2021-04-28 00:00:00.000 | 183 |
20 | 2021-04-29 00:00:00.000 | 184 |
pietrektAddresses with RUNE balance >= 1k
Updated 22 hours ago
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
›
⌄
WITH transactions AS (SELECT to_address as address, amount_e8 as amount, to_date(block_timestamp) as date
FROM thorchain.core.fact_transfer_events as c1 WHERE c1.asset = 'THOR.RUNE' UNION ALL
SELECT from_address as address, amount_e8*(-1) as amount, to_date(block_timestamp) as date FROM thorchain.core.fact_transfer_events as c2 WHERE c2.asset = 'THOR.RUNE'),
daily_change AS (SELECT SUM(amount) as change, date, address FROM transactions GROUP BY date, address ORDER BY date),
daily_change_indexed AS (SELECT date, address, change, ROW_NUMBER() OVER(PARTITION BY address ORDER BY date) AS rownum FROM daily_change ORDER BY date),
daily_change_balance AS (SELECT date, address, change, (SELECT SUM(change) FROM daily_change_indexed AS c2 WHERE c2.address = c1.address AND c2.rownum <= c1.rownum) AS balance FROM daily_change_indexed AS c1 ORDER BY date),
--SELECT * FROM daily_change_balance
dates AS (SELECT DISTINCT date, 0 as C FROM daily_change_balance ORDER BY date),
rich_increase AS (SELECT date, COUNT(*) as C FROM daily_change_balance as c1 WHERE c1.balance >= 100000000000 AND c1.balance - c1.change < 100000000000 GROUP BY date ORDER BY date),
rich_decrease AS (SELECT date, COUNT(*) * (-1) as C FROM daily_change_balance as c1 WHERE c1.balance < 100000000000 AND c1.balance - c1.change >= 100000000000 GROUP BY date ORDER BY date),
combination AS (SELECT * FROM dates UNION ALL SELECT * FROM rich_increase UNION ALL SELECT * FROM rich_decrease),
rich_change AS (SELECT date, SUM(C) AS change, ROW_NUMBER() OVER(ORDER BY date) as rownum FROM combination GROUP BY date ORDER BY date)
SELECT date as day, (SELECT SUM(change) FROM rich_change AS c2 WHERE c2.rownum <= c1.rownum) as rich_count FROM rich_change c1 WHERE day is not null ORDER BY day
Last run: about 22 hours ago
...
1522
49KB
13s