Re^3: More PM stats analysis on new levels (large query result)

[demerphq]

Well I put together the following query for you. I don't think its exactly what you had in mind, but its more than nothing. Its a breakdown of posts by type by level of author. Of course its by level of author _now_, not when originally posted. It does not include reaped nodes.

---

select t.title typetitle, lb.level, count(n.node_id) nodecount
from node n, node a, user u, node t, level_buckets lb
where n.author_user = a.node_id
and   n.type_nodetype = t.node_id
and   a.node_id = u.user_id
and   CEIL(u.experience/10)*10 = lb.experience
and n.author_user != 52855
and n.type_nodetype in (31670, 1042, 31663, 1036, 11, 935, 1588, 173295, 121, 120, 23614, 23615, 115,
956, 389544, 1584, 337433, 1440, 7487, 7488, 1980, 1981, 1748, 1749)
group by t.title, lb.level
order by t.title, lb.level

(351 rows)

typetitle	level	nodecount
bookreview	1	1
bookreview	2	1
bookreview	3	2
bookreview	4	1
bookreview	5	1
bookreview	6	5
bookreview	7	7
bookreview	8	16
bookreview	9	4
bookreview	10	12
bookreview	11	4
bookreview	12	2
bookreview	13	13
bookreview	14	4
bookreview	15	6
bookreview	16	3
bookreview	17	2
bookreview	18	7
bookreview	19	14
bookreview	21	3
bookreview	23	1
CUFP	1	56
CUFP	2	33
CUFP	3	24
CUFP	4	22
CUFP	5	33
CUFP	6	49
CUFP	7	58
CUFP	8	82
CUFP	9	92
CUFP	10	62
CUFP	11	84
CUFP	12	29
CUFP	13	121
CUFP	14	67
CUFP	15	47
CUFP	16	81
CUFP	17	58
CUFP	18	44
CUFP	19	41
CUFP	20	28
CUFP	21	21
CUFP	22	10
CUFP	23	8
CUFP	28	2
modulereview	1	5
modulereview	2	2
modulereview	5	3
modulereview	6	4
modulereview	7	4
modulereview	8	3
modulereview	9	9
modulereview	10	2
modulereview	11	2
modulereview	12	13
modulereview	13	16
modulereview	14	13
modulereview	15	3
modulereview	16	2
modulereview	17	5
modulereview	18	8
modulereview	19	15
modulereview	20	3
modulereview	21	4
modulereview	22	3
monkdiscuss	1	166
monkdiscuss	2	36
monkdiscuss	3	26
monkdiscuss	4	29
monkdiscuss	5	44
monkdiscuss	6	103
monkdiscuss	7	113
monkdiscuss	8	161
monkdiscuss	9	143
monkdiscuss	10	216
monkdiscuss	11	161
monkdiscuss	12	113
monkdiscuss	13	285
monkdiscuss	14	264
monkdiscuss	15	237
monkdiscuss	16	148
monkdiscuss	17	172
monkdiscuss	18	132
monkdiscuss	19	67
monkdiscuss	20	97
monkdiscuss	21	46
monkdiscuss	22	61
monkdiscuss	23	53
monkdiscuss	28	148
note	1	26159
note	2	6614
note	3	5294
note	4	4624
note	5	6983
note	6	10719
note	7	11449
note	8	17569
note	9	19656
note	10	18919
note	11	19641
note	12	15056
note	13	33144
note	14	27905
note	15	24871
note	16	21586
note	17	20069
note	18	18754
note	19	15114
note	20	13200
note	21	15864
note	22	20531
note	23	14006
note	28	329
obfuscated	1	105
obfuscated	2	46
obfuscated	3	51
obfuscated	4	42
obfuscated	5	59
obfuscated	6	88
obfuscated	7	89
obfuscated	8	118
obfuscated	9	147
obfuscated	10	128
obfuscated	11	134
obfuscated	12	69
obfuscated	13	111
obfuscated	14	88
obfuscated	15	64
obfuscated	16	50
obfuscated	17	34
obfuscated	18	26
obfuscated	19	20
obfuscated	20	37
obfuscated	21	19
obfuscated	22	7
obfuscated	23	4
perlcraft	1	19
perlcraft	2	15
perlcraft	3	5
perlcraft	4	7
perlcraft	5	15
perlcraft	6	21
perlcraft	7	10
perlcraft	8	24
perlcraft	9	21
perlcraft	10	15
perlcraft	11	17
perlcraft	12	6
perlcraft	13	35
perlcraft	14	6
perlcraft	15	11
perlcraft	16	7
perlcraft	17	7
perlcraft	18	3
perlcraft	19	5
perlcraft	20	4
perlcraft	21	6
perlcraft	23	1
perlmeditation	1	142
perlmeditation	2	42
perlmeditation	3	35
perlmeditation	4	43
perlmeditation	5	82
perlmeditation	6	132
perlmeditation	7	144
perlmeditation	8	206
perlmeditation	9	256
perlmeditation	10	251
perlmeditation	11	278
perlmeditation	12	175
perlmeditation	13	433
perlmeditation	14	400
perlmeditation	15	391
perlmeditation	16	227
perlmeditation	17	349
perlmeditation	18	213
perlmeditation	19	173
perlmeditation	20	141
perlmeditation	21	129
perlmeditation	22	155
perlmeditation	23	224
perlmeditation	28	6
perlnews	1	166
perlnews	2	8
perlnews	3	16
perlnews	4	4
perlnews	5	9
perlnews	6	7
perlnews	7	8
perlnews	8	32
perlnews	9	35
perlnews	10	29
perlnews	11	40
perlnews	12	21
perlnews	13	75
perlnews	14	83
perlnews	15	46
perlnews	16	66
perlnews	17	50
perlnews	18	63
perlnews	19	60
perlnews	20	19
perlnews	21	19
perlnews	22	24
perlnews	23	20
perlnews	28	5
perlquestion	1	13253
perlquestion	2	4567
perlquestion	3	3148
perlquestion	4	2243
perlquestion	5	2608
perlquestion	6	2999
perlquestion	7	2501
perlquestion	8	2934
perlquestion	9	2224
perlquestion	10	1805
perlquestion	11	1898
perlquestion	12	1038
perlquestion	13	1839
perlquestion	14	1179
perlquestion	15	1260
perlquestion	16	559
perlquestion	17	818
perlquestion	18	245
perlquestion	19	189
perlquestion	20	172
perlquestion	21	78
perlquestion	22	126
perlquestion	23	264
perlquestion	28	24
perltutorial	1	3
perltutorial	3	1
perltutorial	4	3
perltutorial	5	5
perltutorial	6	29
perltutorial	7	3
perltutorial	8	3
perltutorial	9	8
perltutorial	10	5
perltutorial	11	8
perltutorial	12	3
perltutorial	13	14
perltutorial	14	17
perltutorial	15	13
perltutorial	16	17
perltutorial	17	7
perltutorial	18	5
perltutorial	19	8
perltutorial	20	3
perltutorial	21	18
perltutorial	22	1
perltutorial	23	3
perltutorial	28	2
pmdevtopic	10	2
pmdevtopic	11	2
pmdevtopic	12	3
pmdevtopic	13	1
pmdevtopic	14	1
pmdevtopic	15	2
pmdevtopic	16	11
pmdevtopic	17	2
pmdevtopic	18	6
pmdevtopic	20	17
pmdevtopic	21	1
pmdevtopic	22	3
poem	1	100
poem	2	49
poem	3	35
poem	4	21
poem	5	31
poem	6	53
poem	7	61
poem	8	64
poem	9	79
poem	10	64
poem	11	54
poem	12	14
poem	13	89
poem	14	43
poem	15	47
poem	16	34
poem	17	17
poem	18	24
poem	19	5
poem	20	3
poem	21	16
poem	22	4
poem	23	4
poll	1	3
poll	5	1
poll	6	37
poll	9	3
poll	10	1
poll	13	1
poll	14	21
poll	15	6
poll	16	4
poll	17	15
poll	18	2
poll	20	11
poll	22	1
poll	28	108
quest	28	13
snippet	1	99
snippet	2	67
snippet	3	28
snippet	4	33
snippet	5	42
snippet	6	69
snippet	7	52
snippet	8	113
snippet	9	79
snippet	10	83
snippet	11	74
snippet	12	40
snippet	13	122
snippet	14	94
snippet	15	67
snippet	16	65
snippet	17	82
snippet	18	124
snippet	19	39
snippet	20	47
snippet	21	41
snippet	22	47
snippet	23	86
snippet	28	1
sourcecode	1	80
sourcecode	2	57
sourcecode	3	47
sourcecode	4	35
sourcecode	5	46
sourcecode	6	87
sourcecode	7	65
sourcecode	8	75
sourcecode	9	89
sourcecode	10	110
sourcecode	11	123
sourcecode	12	27
sourcecode	13	144
sourcecode	14	69
sourcecode	15	55
sourcecode	16	34
sourcecode	17	72
sourcecode	18	77
sourcecode	19	36
sourcecode	20	34
sourcecode	21	29
sourcecode	22	20
sourcecode	23	21
sourcecode	28	3

And this is the breakdown of the notes by the type of the root node of the thread.

---

select t.title typetitle, lb.level, count(n.node_id) nodecount
from node n,note,node nt,node a, user u, node t, level_buckets lb
where note.note_id = nt.node_id
and n.node_id = note.root_node
and nt.type_nodetype = 11
and nt.author_user = a.node_id
and n.type_nodetype = t.node_id
and a.node_id = u.user_id
and CEIL(u.experience/10)*10 = lb.experience
and nt.author_user != 52855
and n.type_nodetype in (31670, 1042, 31663, 1036, 11, 935, 1588, 173295, 121, 120, 23614, 23615, 115,
956, 389544, 1584, 337433, 1440, 7487, 7488, 1980, 1981, 1748, 1749)
group by t.title, lb.level
order by t.title, lb.level

(381 rows)

typetitle	level	nodecount
bookreview	1	23
bookreview	2	5
bookreview	3	3
bookreview	4	4
bookreview	5	7
bookreview	6	11
bookreview	7	11
bookreview	8	18
bookreview	9	17
bookreview	10	21
bookreview	11	20
bookreview	12	11
bookreview	13	30
bookreview	14	17
bookreview	15	26
bookreview	16	9
bookreview	17	14
bookreview	18	13
bookreview	19	29
bookreview	20	5
bookreview	21	7
bookreview	22	12
bookreview	23	6
CUFP	1	320
CUFP	2	83
CUFP	3	49
CUFP	4	57
CUFP	5	86
CUFP	6	119
CUFP	7	140
CUFP	8	264
CUFP	9	225
CUFP	10	210
CUFP	11	213
CUFP	12	128
CUFP	13	366
CUFP	14	281
CUFP	15	226
CUFP	16	284
CUFP	17	238
CUFP	18	186
CUFP	19	128
CUFP	20	63
CUFP	21	158
CUFP	22	129
CUFP	23	152
CUFP	28	5
modulereview	1	48
modulereview	2	9
modulereview	4	2
modulereview	5	3
modulereview	6	17
modulereview	7	7
modulereview	8	8
modulereview	9	19
modulereview	10	11
modulereview	11	20
modulereview	12	19
modulereview	13	25
modulereview	14	35
modulereview	15	24
modulereview	16	15
modulereview	17	21
modulereview	18	10
modulereview	19	26
modulereview	20	5
modulereview	21	14
modulereview	22	20
modulereview	23	10
modulereview	28	2
monkdiscuss	1	1062
monkdiscuss	2	79
monkdiscuss	3	112
monkdiscuss	4	101
monkdiscuss	5	167
monkdiscuss	6	421
monkdiscuss	7	468
monkdiscuss	8	829
monkdiscuss	9	963
monkdiscuss	10	1310
monkdiscuss	11	1136
monkdiscuss	12	934
monkdiscuss	13	2194
monkdiscuss	14	1973
monkdiscuss	15	1411
monkdiscuss	16	1528
monkdiscuss	17	1421
monkdiscuss	18	1086
monkdiscuss	19	739
monkdiscuss	20	932
monkdiscuss	21	1066
monkdiscuss	22	1373
monkdiscuss	23	473
monkdiscuss	28	173
note	1	2
note	10	1
note	14	1
note	16	1
note	18	2
obfuscated	1	263
obfuscated	2	40
obfuscated	3	60
obfuscated	4	54
obfuscated	5	71
obfuscated	6	149
obfuscated	7	149
obfuscated	8	198
obfuscated	9	303
obfuscated	10	347
obfuscated	11	189
obfuscated	12	153
obfuscated	13	285
obfuscated	14	221
obfuscated	15	205
obfuscated	16	234
obfuscated	17	93
obfuscated	18	159
obfuscated	19	70
obfuscated	20	54
obfuscated	21	103
obfuscated	22	62
obfuscated	23	44
obfuscated	28	4
perlcraft	1	48
perlcraft	2	7
perlcraft	3	6
perlcraft	4	13
perlcraft	5	13
perlcraft	6	35
perlcraft	7	21
perlcraft	8	37
perlcraft	9	44
perlcraft	10	38
perlcraft	11	54
perlcraft	12	22
perlcraft	13	76
perlcraft	14	46
perlcraft	15	50
perlcraft	16	44
perlcraft	17	32
perlcraft	18	11
perlcraft	19	21
perlcraft	20	12
perlcraft	21	32
perlcraft	22	45
perlcraft	23	36
perlmeditation	1	3298
perlmeditation	2	231
perlmeditation	3	252
perlmeditation	4	274
perlmeditation	5	587
perlmeditation	6	1070
perlmeditation	7	1099
perlmeditation	8	2021
perlmeditation	9	2472
perlmeditation	10	2663
perlmeditation	11	2919
perlmeditation	12	2041
perlmeditation	13	4245
perlmeditation	14	5018
perlmeditation	15	3414
perlmeditation	16	3350
perlmeditation	17	3354
perlmeditation	18	2675
perlmeditation	19	2014
perlmeditation	20	2935
perlmeditation	21	2615
perlmeditation	22	4126
perlmeditation	23	2172
perlmeditation	28	13
perlnews	1	222
perlnews	2	11
perlnews	3	21
perlnews	4	7
perlnews	5	25
perlnews	6	37
perlnews	7	24
perlnews	8	59
perlnews	9	90
perlnews	10	86
perlnews	11	120
perlnews	12	67
perlnews	13	176
perlnews	14	300
perlnews	15	180
perlnews	16	193
perlnews	17	143
perlnews	18	192
perlnews	19	119
perlnews	20	104
perlnews	21	130
perlnews	22	86
perlnews	23	107
perlnews	28	1
perlquestion	1	19147
perlquestion	2	5776
perlquestion	3	4546
perlquestion	4	3829
perlquestion	5	5652
perlquestion	6	8203
perlquestion	7	8793
perlquestion	8	13043
perlquestion	9	14392
perlquestion	10	13112
perlquestion	11	13942
perlquestion	12	10997
perlquestion	13	23976
perlquestion	14	18495
perlquestion	15	18255
perlquestion	16	14768
perlquestion	17	13897
perlquestion	18	13572
perlquestion	19	11432
perlquestion	20	8763
perlquestion	21	11056
perlquestion	22	13968
perlquestion	23	10297
perlquestion	28	82
perltutorial	1	272
perltutorial	2	23
perltutorial	3	15
perltutorial	4	14
perltutorial	5	22
perltutorial	6	30
perltutorial	7	26
perltutorial	8	40
perltutorial	9	40
perltutorial	10	38
perltutorial	11	37
perltutorial	12	42
perltutorial	13	72
perltutorial	14	60
perltutorial	15	63
perltutorial	16	45
perltutorial	17	40
perltutorial	18	49
perltutorial	19	52
perltutorial	20	24
perltutorial	21	40
perltutorial	22	34
perltutorial	23	27
perltutorial	28	1
pmdevtopic	1	1
pmdevtopic	2	1
pmdevtopic	4	6
pmdevtopic	6	1
pmdevtopic	7	1
pmdevtopic	8	2
pmdevtopic	9	8
pmdevtopic	10	6
pmdevtopic	11	3
pmdevtopic	12	11
pmdevtopic	13	11
pmdevtopic	14	8
pmdevtopic	15	17
pmdevtopic	16	18
pmdevtopic	17	18
pmdevtopic	18	43
pmdevtopic	19	4
pmdevtopic	20	60
pmdevtopic	21	17
pmdevtopic	22	56
poem	1	159
poem	2	41
poem	3	34
poem	4	23
poem	5	43
poem	6	62
poem	7	111
poem	8	142
poem	9	150
poem	10	134
poem	11	104
poem	12	55
poem	13	177
poem	14	128
poem	15	95
poem	16	85
poem	17	82
poem	18	105
poem	19	28
poem	20	16
poem	21	52
poem	22	24
poem	23	10
poll	1	520
poll	2	153
poll	3	97
poll	4	122
poll	5	158
poll	6	332
poll	7	360
poll	8	533
poll	9	513
poll	10	507
poll	11	426
poll	12	300
poll	13	773
poll	14	683
poll	15	326
poll	16	509
poll	17	304
poll	18	148
poll	19	135
poll	20	45
poll	21	117
poll	22	110
poll	23	65
poll	28	24
quest	1	28
quest	2	8
quest	3	6
quest	4	9
quest	5	21
quest	6	24
quest	7	22
quest	8	49
quest	9	37
quest	10	52
quest	11	32
quest	12	25
quest	13	53
quest	14	39
quest	15	30
quest	16	43
quest	17	14
quest	18	4
quest	19	3
quest	20	5
quest	21	12
quest	22	5
quest	23	3
quest	28	1
snippet	1	280
snippet	2	74
snippet	3	20
snippet	4	57
snippet	5	50
snippet	6	78
snippet	7	85
snippet	8	148
snippet	9	177
snippet	10	183
snippet	11	186
snippet	12	124
snippet	13	322
snippet	14	319
snippet	15	284
snippet	16	255
snippet	17	234
snippet	18	256
snippet	19	185
snippet	20	86
snippet	21	262
snippet	22	307
snippet	23	402
snippet	28	4
sourcecode	1	341
sourcecode	2	59
sourcecode	3	63
sourcecode	4	39
sourcecode	5	63
sourcecode	6	86
sourcecode	7	98
sourcecode	8	138
sourcecode	9	159
sourcecode	10	155
sourcecode	11	190
sourcecode	12	93
sourcecode	13	271
sourcecode	14	207
sourcecode	15	196
sourcecode	16	138
sourcecode	17	112
sourcecode	18	191
sourcecode	19	107
sourcecode	20	81
sourcecode	21	149
sourcecode	22	134
sourcecode	23	155
sourcecode	28	7