SELECT a.username,
(SELECT COUNT(*) FROM accounts ac WHERE ac.downline_id = a. id AND ac.balance>0
AND (ac.registered_date >= '2017-08-01' AND ac.registered_date <= '2017-08-31') AND active=1)
AS 'total_downline' FROM accounts a
ORDER BY
(SELECT COUNT(*) FROM accounts ac WHERE ac.downline_id = a. id AND ac.balance>0
AND (ac.registered_date >= '2017-08-01' AND ac.registered_date <= '2017-08-31') AND active=1)
DESC
LIMIT 10

SELECT a.username,
(SELECT COUNT(*) FROM accounts ac
	WHERE ac.downline_id = a. id
	AND (ac.registered_date >= '2017-08-01' AND ac.registered_date <= '2017-08-31')
	AND active=1
	AND (SELECT COUNT(*) FROM transactions tr WHERE tr.user_id=ac.id AND tr.status=1 AND TYPE='Deposit') >= 1
)
AS 'total_downline' FROM accounts a
ORDER BY
(SELECT COUNT(*) FROM accounts ac
	WHERE ac.downline_id = a. id
	AND (ac.registered_date >= '2017-08-01' AND ac.registered_date <= '2017-08-31')
	AND active=1
	AND (SELECT COUNT(*) FROM transactions tr WHERE tr.user_id=ac.id AND tr.status=1 AND TYPE='Deposit') >= 1
)
DESC
LIMIT 10