Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

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

---
$world=~s/war/peace/g


In reply to Re^3: More PM stats analysis on new levels (large query result) by demerphq
in thread More PM stats analysis on new levels by xdg

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (8)
    As of 2014-07-12 04:10 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      When choosing user names for websites, I prefer to use:








      Results (238 votes), past polls