View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001357 | Main CAcert Website | website content | public | 2015-01-10 14:16 | 2015-02-21 17:53 |
| Reporter | BenBE | Assigned To | BenBE | ||
| Priority | normal | Severity | tweak | Reproducibility | always |
| Status | needs review & testing | Resolution | open | ||
| Product Version | 2015 Q1 | ||||
| Target Version | 2015 Q2 | ||||
| Summary | 0001357: Inaccuracy when calculating the user statistics | ||||
| Description | When having a look at the number of passed CATS tests and the number of assurers over the years the statistic claims we got more assurers then people who have taken the CATS; this is contradictory as the CATS is (as of 2009 at least) mandatory in order to be an assurer. | ||||
| Steps To Reproduce | Have a look at the testserver stats; it's more clearly visible there. According to the CATS table we got 1333 cats tests by 1262 people. The stats claim 3384 assurers since 2010 -> must be wrong. | ||||
| Additional Information | The issue can be solved like this: --- SET @a = '2010-01-01 00:00:00'; SET @b = '2011-01-01 00:00:00'; CREATE TEMPORARY TABLE a SELECT n.`to`, sum(n.awarded) as `received_pts`, max(n.`when`) as `last_assurance` FROM cacert.notary as n WHERE 1 AND n.`from` != n.`to` AND (n.`deleted` = '0000-00-00 00:00:00' OR n.`deleted` >= @a) AND n.`when` < @a AND n.`to` in (SELECT c.user_id FROM cats_passed as c WHERE pass_date < @a) GROUP by n.`to` HAVING 1 AND `received_pts` >= 100 ORDER by `last_assurance` DESC; CREATE TEMPORARY TABLE b SELECT n.`to`, sum(n.awarded) as `received_pts`, max(n.`when`) as `last_assurance` FROM cacert.notary as n WHERE 1 AND n.`from` != n.`to` AND (n.`deleted` = '0000-00-00 00:00:00' OR n.`deleted` >= @b) AND n.`when` < @b AND n.`to` in (SELECT c.user_id FROM cats_passed as c WHERE pass_date < @b) GROUP by n.`to` HAVING 1 AND `received_pts` >= 100 ORDER by `last_assurance` DESC; SELECT b.* FROM b WHERE b.`to` NOT IN (SELECT a.`to` FROM a); DROP TEMPORARY TABLE a; DROP TEMPORARY TABLE b; --- Where the values of @a and @b are the start and end of the timespan of interest. Queries tested on testserver. For dates prior to 2009-01-01 skip the query refering to the CATS table or adopt it accordingly. | ||||
| Tags | No tags attached. | ||||
| Reviewed by | BenBE | ||||
| Test Instructions | I cannot see a good way to test the statistics. | ||||
|
|
I pushed a fix to https://github.com/INOPIAE/CAcert/commit/ea7a56ff5787c4e9d71f3d17f4e93d87745cf92d |
|
|
I went to the statistcs page. I cannot test if the values are correct, but I did check consistency. The numbers over the months sum up to the correct value for both entries (100AP/CATS). -> ok The numbers over the years sum to up to the correct value for both entries (100AP/CATS). -> ok The last two values should be the same is in the first table. First table shows: Assurer Candidates: 3,511 Assurers with test: 1,133 Sums: Sum of 100 AP: 3,512 Sum of CATS: 1,133 As all assurances were performed after CATS was added those values should match. However as this is a test server it could be manipulated for some entries. The CATS it matches. For the 100 AP it is off for 1. This number should be identical. -> INOPIAE wants to fix this. Afterwards it also want to check that if I add assurances and the statistic is recalculated that it matches (would need someone to initiate this). |
|
|
I pushed a new fix to https://github.com/INOPIAE/CAcert/commit/6412d38dd2361032b67330def4702ea04b4263a3 |
|
|
The numbers now add up. But it is hard to understand that the first table counts different things, than the second table. I would prefer, if the first table would also display the users with at least 100 points. => consistent, but could be improved (if the values are correct cannot be tested without access to the testserver, so I cannot give a "ok") |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-01-10 14:16 | BenBE | New Issue | |
| 2015-01-10 14:16 | BenBE | Assigned To | => INOPIAE |
| 2015-01-13 06:30 | INOPIAE | Note Added: 0005232 | |
| 2015-01-13 06:30 | INOPIAE | Assigned To | INOPIAE => BenBE |
| 2015-01-13 06:30 | INOPIAE | Status | new => fix available |
| 2015-01-13 06:31 | INOPIAE | Test Instructions | => I cannot see a good way to test the statistics. |
| 2015-01-13 20:58 | BenBE | Status | fix available => needs review & testing |
| 2015-01-13 20:58 | BenBE | Reviewed by | => BenBE |
| 2015-01-20 22:04 | Eva | Note Added: 0005250 | |
| 2015-01-25 07:07 | INOPIAE | Note Added: 0005263 | |
| 2015-02-03 20:40 | Eva | Note Added: 0005301 | |
| 2015-02-21 17:53 | INOPIAE | Relationship added | related to 0001374 |
