CIP Proposal: Voting Meta Protocol through Broadcasts

Development Counterparty
14 
sqlite> SELECT asset, COUNT(*) FROM debits GROUP BY asset ORDER BY COUNT(*) DESC LIMIT 50;
LTBCOIN|139453
XCP|94997
FLDC|66924
SJCX|21779
GEMZ|15539
BITCRYSTALS|9215

SELECT address, SUM(debits_sum), SUM(credits_sum), SUM(credits_sum) -SUM(debits_sum) AS balance FROM (
	SELECT 
		address, 
		SUM(quantity) AS debits_sum, 
		0 AS credits_sum 
	FROM debits 
	WHERE asset = 'XCP' 
	AND block_index <= 398215 
	GROUP BY address

	UNION

	SELECT 
		address, 
		0 AS debits_sum, 
		SUM(quantity) AS credits_sum 
	FROM credits 
	WHERE asset = 'XCP' 
	AND block_index <= 398215 
	GROUP BY address
)
GROUP BY address
;

takes ~0.5s to run, which is fine imo for when you want to list the results, we could add an index to those tables on asset + block_index instead of just asset, but doesn’t seem like it speeds up the query at this data set size (yet).

util.holders however normally takes into account balances that are in ‘escrow’ (for DEX and bets), which I think is correct, so this query above is insufficient.