SQL Server: How to count elements in ranges specified in a query?

This will return a row for each rank from 0-49999 to the rank in which the greatest number of followers is found.

DECLARE @RangeSize int = 50000;
DECLARE @NumberOfRanges int = (SELECT MAX (FollowersCount) FROM #table) / @RangeSize;

; WITH n (n) AS
(
SELECT 1 UNION ALL SELECT n + 1 FROM n WHERE n <= @NumberOfRanges
), ranges(s,e) AS
(
  SELECT @RangeSize * (n-1), @RangeSize * n FROM n
)
SELECT r.s, r.e-1, COUNT(t.Id)
FROM ranges AS r
LEFT OUTER JOIN #table AS t
ON t.FollowersCount >= r.s
And t.Followers Count <r.e
GROUP BY r.s, r.e
ORDER BY r.s;

If you only want the ranges with a non-zero user count, simply change the LEFT OUTSIDE to INSIDE. And I prefer an open range over BETWEEN for two reasons: one, it is ambiguous, because if someone has exactly 100K, they fall in the 50k – 100k cube, or in the 100k – 150k cube, and two, although it can be forced in any way, does not translate well to other types of data, where it is even more problematic.