hmmm, not simple
But let's see.
1st, let's copy the table and put the data in
Code:
create table mm like boinc_milestone_makers;
insert into mm select * from boinc_milestone_makers;
Now add some columns to make it easier...
Code:
alter table mm add maxwells integer;
alter table mm add maxwellsmax integer;
add a new table to hold the constants
Code:
create table mmmax (mtype char(6), total integer);
And populate it
Code:
insert into mmmax (select mtype,max(score) from boinc_milestone_makers group by mtype);
Fill in the max column so that we are just manipulating a single table
Code:
update mm set mm.maxwellsmax = (select a.total from mmmax a where a.mtype = mm.mtype);
Now we can populate the maxwells
Code:
update mm set maxwells = case when score > maxwellsmax/2 then 1 else 0 end;
And aggregate them...
Code:
mysql> select cpid,nick,count(*) from mm where maxwells = 1 group by cpid order by count(*) desc limit 0,50;
+----------------------------------+----------------------------------+----------+
| cpid | nick | count(*) |
+----------------------------------+----------------------------------+----------+
| 13a87c3a303bcdca4ba0ed600daebb6b | Steve* | 21 |
| c588950ba551b22cf0d96adbf6dcd720 | kevint | 19 |
| 0a7b8378436fdcaef549d87e35f26480 | zombie67 [MM] | 17 |
| 30ef1387a2ce5255fccb1cafad10d434 | [SG]marodeur6 | 17 |
| 8d57633835ae6c0f456992d4a8d64f17 | vaughan | 15 |
| d7a3d9afaf379c6984d47f1d9672f5e2 | UL1 | 14 |
| 7eedd09e6a4480a25abd083794e3d041 | Merlyn (The Scottish Boinc Team) | 13 |
| SETI.USA | NULL | 12 |
| SETI.Germany | NULL | 12 |
| 495cc70f48d30e3627ea295ae05c66cb | [B^S] Ralfy | 12 |
| 13771f38841e3098c9f7777f898bb3df | Liuqyn | 12 |
| 55dede3044505ad8c5c8d482b5bdb7aa | Roald | 11 |
| L'Alliance Francophone | NULL | 11 |
| Czech National Team | NULL | 11 |
| b6af44167dcf084272defee0370b4933 | Swordfish | 10 |
| 16a4a513fbbb77b756173c6d1cf16b44 | Mumps [MM] | 10 |
| 7396e6f5f8a8d380921138b3dc73b6d4 | Fish | 10 |
| 4f6b6d806421e014100a51eda4532383 | Jeff17 | 10 |
| BOINC Synergy | NULL | 9 |
| 2a6ff29b76493b1ceed6dc9b5e561af2 | Whizbang | 9 |
| 3940fc73c7be29288e25fd2c493abd7e | Paul | 9 |
| 15dda3a9d21f03909ae93749dbc25d92 | Pwrguru | 9 |
| 3e60152ecae4692542a0a6b5b0adde22 | glennpat | 9 |
| BOINC@AUSTRALIA | NULL | 9 |
| Ars Technica | NULL | 8 |
| BOINC@Poland | NULL | 8 |
| d6495a51c30f25abc8127442d96dbf2a | Maxwell [MM] | 8 |
| c843714b32e467b97d2571d1af9971f8 | Bok | 8 |
| c74e8d741ce5168b45aa33ccecad3df2 | Tomasz R. Gwiazda | 8 |
| 4f7c18143c4500b79bce33001cba829d | vaio | 7 |
| 80a2c9677fe286336bba0a711478d327 | Al Dente | 7 |
| 1f27313842a36433486e6dcddc52491f | koll | 7 |
| The Knights Who Say Ni! | NULL | 7 |
| 68193ff085b019ffc693e5914502b0f6 | Nasicus | 7 |
| BOINCstats | NULL | 7 |
| 8e0b099acd93adc95684151a4eef9b86 | R Bruce Kennedy | 7 |
| Planet 3DNow! | NULL | 7 |
| Russia | NULL | 6 |
| 5b9257f09ca25cb7e702caa82f663a39 | Temujin | 6 |
| 4b0a4762e72ff7dfbc6384b64aba4722 | Traviss | 6 |
| b70b4c2ce3dd0ada9aecc5f02354c01b | nenym | 6 |
| 7d66b07c7d278c751e1ca88ec1c9c0a8 | Persch, Patrick J. | 6 |
| 7e08ea3c1224da719464e8946f705bbe | user312 | 6 |
| f7e8c5dfbccfce7e6054e7a84c151aa4 | {KWSN}John Galt 007 | 6 |
| 31cacd407a7850b810ed201a2f7f85a3 | Greeri | 6 |
| ca0eda29adb1043c45fda3f69a6b3466 | [boinc.at] Fireman69 | 6 |
| 433903a71f39cd5ccccbc22a812ccfe6 | BlkJack-21 | 6 |
| 36109d256fc06c7f7e9f8fdb55a73de9 | Cyph3r | 5 |
| 57e98e51213986130bf3c136f9db957e | Dingo | 5 |
| a848091602d6dade8ee661252d106d2d | rilian | 5 |
+----------------------------------+----------------------------------+----------+
50 rows in set (1.36 sec)
Note that this is for all categories regardless, including the top 10's and team mm's too.
Is this what you expected ?
Might be a simpler way to do it and I'll have to code it into a perl script to run it nightly. Would only take a few seconds total to run I suspect, there is ~ 100,000 rows in the MM tables so not too much data in the grand scheme of things.