A suggestion for a slightly silly but mysterious one

[Bok]

subprojects are a different table so it that complicates..

I'm not sure you have them all correct, for instance 99995 and 10050, in fact all of your ones like that have a 5 tolerance on the prior but much more on the post side

Also don't really need the truncate part when we are doing between.

This one though gives this subset

+----------------------------------+---------------+---------+
| cpid                             | nick          | mycount |
+----------------------------------+---------------+---------+
| e33013c782482e80caf0c65b476a2d87 | JayPi         |      24 |
| 476d777382a3555be83f6af9ab59c9f9 | Szopler       |      22 |
| 61c4eaaca6667bc9dda3412188c5f173 | x3mEn         |      18 |
| 0dd9749ec16c8900f065d871290156bc | BAR           |      18 |
| 5dad1593a3bd6cd31ce4a9b507ba20fc | Ananas        |      12 |
| 3bfe3268986a7fe9ebaa5acc02e69393 | somanyroads   |       9 |
| 7907e81b12e62de3e9a0c5459a973318 | Mark Gallaher |       9 |
| 5ce7cfc3ff8b21f50b0eb24a7d29ffcd | FourOh        |       9 |
| d7a3d9afaf379c6984d47f1d9672f5e2 | UL1           |       8 |
| 8e0b099acd93adc95684151a4eef9b86 | Bruce Kennedy |       7 |
+----------------------------------+---------------+---------+
10 rows in set (21.496 sec)

[MarkRBright]

subprojects are a different table so it that complicates..
I'm not sure you have them all correct, for instance 99995 and 10050, in fact all of your ones like that have a 5 tolerance on the prior but much more on the post side

Ah yes, sorry - I had a rush of blood just after 1am and started doing that. I was tired and started copying and pasting and just adding zeros. Never a good time for my best work - apologies (I guess I lose the chance of being a subcontractor! ;-), not that I have touched SQL for nearly 30 years anyway
So, whilst I think we could expect a good few more entries if it were corrected and if Subcontracts were added, Nebbers looks like being a fun enough one to me. What do you think?
I'll correct my mistakes and return it later today.
And what were you doing checking it out at 3:49am or is that my time?

[MarkRBright]

Apologies again for my stupid errors in that, that is probably the first time in all my years of programming that I didn't know how to calculate the values at runtime from an array of fixed values and a parameterised percentage tolerance! Bring back Cobol! Hey ho, it was fun but this ought to be a bit better, hopefully even right.

MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500

or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000

or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000

or metric1 between 24987500 and 25012500
or metric1 between 49950000 and 50050000
or metric1 between 99950000 and 100050000

or metric1 between 249875000 and 250125000
or metric1 between 499500000 and 500500000
or metric1 between 999500000 and 1000500000

or metric1 between 2498750000 and 2501250000
or metric1 between 4995000000 and 5005000000
or metric1 between 9995000000 and 10005000000

or metric1 between 24987500000 and 25012500000
or metric1 between 49950000000 and 50050000000
or metric1 between 99950000000 and 100050000000

group by cpid order by mycount desc limit 0,10;

I assume you can just copy and paste and change the names for SubProjects but I don't know.

[Bok]

I do think it's a good idea yes, just have to figure out the logistics of combining the subproject data also (probably just have a table with both columns calculated separately then add rather than using a union).

+----------------------------------+--------------+---------+
| cpid                             | nick         | mycount |
+----------------------------------+--------------+---------+
| 3a045e428e45f087f7cc832bdb4791ff | RAD-Poland   |      84 |
| e33013c782482e80caf0c65b476a2d87 | JayPi        |      73 |
| 89e772f65716bba7c962b4a13d14e350 | AMDave       |      58 |
| 61c4eaaca6667bc9dda3412188c5f173 | x3mEn        |      49 |
| 0dd9749ec16c8900f065d871290156bc | BAR          |      48 |
| 2286c5351f0f2d2f5d7fee45967f22b8 | Baryna.ch    |      47 |
| 476d777382a3555be83f6af9ab59c9f9 | Szopler      |      42 |
| 5e450cd04783a7e0229107be75832fd0 | Dandelion    |      41 |
| f337e6cc88d1503620abd08a2908ffb4 | Werinbert    |      39 |
| ed8ec64bc4941cd7f255ae589778e1e1 | silent Float |      38 |
+----------------------------------+--------------+---------+
10 rows in set (17.205 sec)

[MarkRBright]

OK then I'll leave it in your capable hands.

[MarkRBright]

Gah! I mad another mistake in it with putting zeros and 9's in the wrong places, it should be

MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500

or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000

or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000

or metric1 between 24875000 and 25125000
or metric1 between 49950000 and 50050000
or metric1 between 99500000 and 100500000

or metric1 between 248750000 and 251250000
or metric1 between 499500000 and 500500000
or metric1 between 995000000 and 1005000000

or metric1 between 2487500000 and 2512500000
or metric1 between 4995000000 and 5005000000
or metric1 between 9950000000 and 10050000000

or metric1 between 24875000000 and 25125000000
or metric1 between 49950000000 and 50050000000
or metric1 between 99500000000 and 100500000000

group by cpid order by mycount desc limit 0,25;

This highlights the benefits of being able to parameterise it and work it out automatically at run time! I have no idea if SQL allows this but it would be good if it did. Anyway - sorry for messing you about, I am fairly sure it is right now.

[MarkRBright]

Double ARGGGH! So I was just sorting out a test for this in a spreadsheet, and I was horrified to see that there were still some differences once I had built some formulas - which I think just goes to show how tricky it will be for people to work out why the have suddenly gotten whatever number of Nebbers they get :-) Anyway according to my spreadsheet your SQL statement should be as follows :

select cpid,nick,count(*) as mycount from boinc_user
where
metric1 between 995 and 1005
or metric1 between 9950 and 10050
or metric1 between 24875 and 25125
or metric1 between 49750 and 50250
or metric1 between 99500 and 100500

or metric1 between 248750 and 251250
or metric1 between 497500 and 502500
or metric1 between 746250 and 753750
or metric1 between 995000 and 1005000

or metric1 between 2487500 and 2512500
or metric1 between 4975000 and 5025000
or metric1 between 9950000 and 10050000

or metric1 between 24875000 and 25125000
or metric1 between 49750000 and 50250000
or metric1 between 99500000 and 100500000

or metric1 between 248750000 and 251250000
or metric1 between 497500000 and 502500000
or metric1 between 995000000 and 1005000000

or metric1 between 2487500000 and 2512500000
or metric1 between 4975000000 and 5025000000
or metric1 between 9950000000 and 10050000000

or metric1 between 24875000000 and 25125000000
or metric1 between 49750000000 and 50250000000
or metric1 between 99500000000 and 100500000000

or metric1 between 248750000000 and 251250000000
or metric1 between 497500000000 and 502500000000
or metric1 between 995000000000 and 1005000000000

group by cpid order by mycount desc limit 0,25;

I also chucked another band on the end as there are probably some people out there with trillions.
OK I will shut up for a bit now, and stop making blattantly obviuos mustakes (hopfully)