A suggestion for a slightly silly but mysterious one

[MarkRBright]

I feel that it wouldn't really be worth doing if there were going to be so few of them - so I wouldn't bother unless tolerances are doable. Just had a quick look at BAR and it appears that whoever he or she is, ramped up a bunch of Trigggls and stopped and may not have Boinced for some time so this could be wasted on them!!! (On that note, would it be easy to introduce a Date Last Seen under the current date First Seen in the headers - perhaps you already track this but don't show it? Obviously you do track the hosts but that would be a much more long winded approach, and you could just update a date when you update any other stuff in the daily update. When displaying you could just show "Unknown" if the date is blank - Sorry - I digress)
In your example above I would argue that it should have been between 99990 and 100010 for the second one, though after seeing it 0.5% (maybe more?) might be more interesting, as more people would get them and quite possibly have no clue as to why. Why anyone think they would get 'rewarded' for a subproject reaching 99952, 499751 or 24876 credits after all? That would be more interesting. Maybe you should delete this thread once you have decided ;-)

[MarkRBright]

Just in case it helps you Bok, I knocked this up - it doesn't take into account SubProjects but I suspect a bit of copy and paste would do so in a few seconds. I went for a 0.5% tolerance. Why not see how long it takes to run and decide if it's doable or not

MariaDB [static]> select cpid,nick,count(*) as mycount from boinc_user
where
(truncate(metric1,0) between 9995 and 10005)
or (truncate(metric1,0) between 24975 and 25025)
or (truncate(metric1,0) between 49950 and 50050)
or (truncate(metric1,0) between 99995 and 10050)
or (truncate(metric1,0) between 249750 and 250250)
or (truncate(metric1,0) between 499500 and 500500)
or (truncate(metric1,0) between 749250 and 750750)
or (truncate(metric1,0) between 999995 and 1000050)
or (truncate(metric1,0) between 2497500 and 2502500)
or (truncate(metric1,0) between 4995000 and 5005000)
or (truncate(metric1,0) between 9999995 and 10000050)
or (truncate(metric1,0) between 24975000 and 25025000)
or (truncate(metric1,0) between 49950000 and 50050000)
or (truncate(metric1,0) between 99999995 and 100000050)
or (truncate(metric1,0) between 249750000 and 250250000)
or (truncate(metric1,0) between 499500000 and 500500000)
or (truncate(metric1,0) between 999999995 and 1000000050)
or (truncate(metric1,0) between 2497500000 and 2502500000)
or (truncate(metric1,0) between 4995000000 and 5005000000)
or (truncate(metric1,0) between 9999999995 and 10000000050)
or (truncate(metric1,0) between 24975000000 and 25025000000)
or (truncate(metric1,0) between 49950000000 and 50050000000)
or (truncate(metric1,0) between 99999999995 and 100000000500)
or (truncate(metric1,0) between 249750000000 and 250250000000)
or (truncate(metric1,0) between 499500000000 and 500500000000)
or (truncate(metric1,0) between 999999999995 and 1000000000050)
or (truncate(metric1,0) between 2497500000000 and 2502500000000)
or (truncate(metric1,0) between 4995000000000 and 5005000000000)
or (truncate(metric1,0) between 9999999999995 and 10000000000050)

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

[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.