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),

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 >= 1000000000000 AND c1.balance - c1.change < 1000000000000 GROUP BY date ORDER BY date),

rich_decrease AS (SELECT date, COUNT(*) * (-1) as C FROM daily_change_balance as c1 WHERE c1.balance < 1000000000000 AND c1.balance - c1.change >= 1000000000000 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 date