require having visited the site in the most recent N weeks
That helps give the recent average, which might be interesting, assuming the average has changed. But that's hard to detect, unless included when people first logged on or started posting.
having made a minimum number of posts
That's a decent idea. The question is, what is the minimum?
To get into SioB, a certain amount of reputation must be reached, that is, 3000. There is no minimum on posts.
Writeups
AS
(
SELECT
GROUPING(Monk_Level) RU,
Monk_Level,
ROUND(MIN(Writeups)) Min,
ROUND(MAX(Writeups)) Max,
ROUND(AVG(Writeups)) Avg,
ROUND(MEDIAN(Writeups)) Median
FROM
Data_Level_Calc
WHERE
-- Exclude those not representative of their group.
Name NOT IN ('vroom', 'NodeReaper')
GROUP BY
ROLLUP(Monk_Level)
)
SELECT
Writeups.Monk_Level,
COUNT(*) Monk_Total,
Writeups.Min,
Writeups.Max,
Writeups.Avg,
Writeups.Median,
COUNT(CASE WHEN DLC.Writeups < Writeups.Avg THEN 1 END) Lower,
COUNT(CASE WHEN DLC.Writeups = Writeups.Avg THEN 1 END) Equal,
COUNT(CASE WHEN DLC.Writeups > Writeups.Avg THEN 1 END) Higher
FROM
Data_Level_Calc DLC,
Writeups
WHERE
Writeups.Monk_Level = DLC.Monk_Level
OR Writeups.RU = 1
--
AND Name NOT IN ('vroom', 'NodeReaper')
GROUP BY
Writeups.Monk_Level,
Writeups.Min,
Writeups.Max,
Writeups.Avg,
Writeups.Median
ORDER BY
Writeups.Monk_Level;
MONK_LEVEL MONK_TOTAL MIN MAX AVG MEDIAN
+ LOWER EQUAL HIGHER
---------- ---------- ---------- ---------- ---------- ---------- ----
+------ ---------- ----------
13 205 0 903 238 229
+ 109 2 94
14 145 1 1009 328 312
+ 75 0 70
15 80 4 1236 456 445
+ 42 0 38
16 77 0 1133 553 538
+ 40 0 37
17 44 13 1582 751 767
+ 21 0 23
18 42 51 2565 936 859
+ 23 0 19
19 34 16 4183 1374 1347
+ 18 0 16
20 26 186 4500 2021 2057
+ 13 0 13
21 18 600 5335 3065 2974
+ 10 0 8
22 8 607 4520 3159 3650
+ 3 0 5
23 6 3416 7240 5503 5794
+ 2 0 4
24 2 3001 6420 4711 4711
+ 1 0 1
25 3 6323 7909 6956 6635
+ 2 0 1
28 4 10463 22669 17635 19772
+ 2 0 2
692 0 22669 785 378
+ 531 0 161
The average Writeups per level does seem to be mostly balanced. (Included the median, per LanX's suggestion.) How exactly would we determine the lower bar for Writeups?
deduct 0.5 XP per day in an attempt to get closer to the XP gained from posting rather than longevity.
That's an intriguing idea. On what is the half point based though?