This is fairly rare in the big scheme of things: (not accounting for quoting yet cause we really need to normalize that data so it queryable first, which I 101% support)
5 or more replies, more that 15% total
select concat('http://bbs.boingboing.net/t/x/', t.id::varchar), u.username as offender, u2.username as offensie, count(*), posts_count
from posts p
join topics t on t.id = p.topic_id
join posts p2 on p2.post_number = p.reply_to_post_number
and t.id = p2.topic_id
join users u on u.id = p.user_id
join users u2 on u2.id = p2.user_id
where archetype <> 'private_message'
and category_id in (6,5,7,8,3,9,1,4)
group by t.id, u.username, u2.username, posts_count
having count(*) > 4 and count(*)::float / posts_count::float > 0.15
order by count(*)::float / posts_count::float desc;
concat offender offensie count posts_count
----------------------------------- --------------- --------------- ----- -----------
http://bbs.boingboing.net/t/x/14506 nonfer Rindan 5 12
http://bbs.boingboing.net/t/x/19200 ActionAbe Mindysan33 9 22
http://bbs.boingboing.net/t/x/27632 PrestonSturges TheBaron 5 13
http://bbs.boingboing.net/t/x/19200 Mindysan33 ActionAbe 8 22
http://bbs.boingboing.net/t/x/22967 miasm Mindysan33 5 14
http://bbs.boingboing.net/t/x/24708 GilbertWham angrydroid 6 17
http://bbs.boingboing.net/t/x/9714 codinghorror Cowicide 6 19
http://bbs.boingboing.net/t/x/9126 William_Holz chenille 7 23
http://bbs.boingboing.net/t/x/24708 angrydroid GilbertWham 5 17
http://bbs.boingboing.net/t/x/12541 bzishi sargemisfit 5 17
http://bbs.boingboing.net/t/x/21044 Mindysan33 noahdjango 7 25
http://bbs.boingboing.net/t/x/21044 noahdjango Mindysan33 7 25
http://bbs.boingboing.net/t/x/14174 robulus urbanistica 5 19
http://bbs.boingboing.net/t/x/9714 Cowicide codinghorror 5 19
http://bbs.boingboing.net/t/x/28010 marilove samsa 5 20
http://bbs.boingboing.net/t/x/22043 William_Holz AliceWeir 10 40
http://bbs.boingboing.net/t/x/8571 teapot Cowicide 6 24
http://bbs.boingboing.net/t/x/10321 Robert_Douglass PatRx2 7 29
http://bbs.boingboing.net/t/x/2277 PurpleStater marilove 5 22
http://bbs.boingboing.net/t/x/15946 Daneyul SteampunkBanana 7 31
http://bbs.boingboing.net/t/x/7840 Medievalist snig 5 23
http://bbs.boingboing.net/t/x/27326 Cowicide teapot 5 23
http://bbs.boingboing.net/t/x/23196 AcerPlatanoides Peacen1k 6 28
http://bbs.boingboing.net/t/x/25559 Cowicide bwv812 10 47
http://bbs.boingboing.net/t/x/16109 AliceWeir Kimmo 5 24
http://bbs.boingboing.net/t/x/16109 Kimmo AliceWeir 5 24
http://bbs.boingboing.net/t/x/22043 AliceWeir William_Holz 8 40
http://bbs.boingboing.net/t/x/11183 Cowicide Space_Monkey 8 41
http://bbs.boingboing.net/t/x/25559 bwv812 Cowicide 9 47
http://bbs.boingboing.net/t/x/27491 Cowicide teapot 9 47
http://bbs.boingboing.net/t/x/15187 peregrinus_bis GilbertWham 8 42
http://bbs.boingboing.net/t/x/15187 GilbertWham peregrinus_bis 8 42
http://bbs.boingboing.net/t/x/23651 rattypilgrim NickyG 6 32
http://bbs.boingboing.net/t/x/5757 caryroys dacree 8 43
http://bbs.boingboing.net/t/x/15548 bwv812 teapot 5 27
http://bbs.boingboing.net/t/x/24234 Mindysan33 niczar 5 27
http://bbs.boingboing.net/t/x/15548 teapot bwv812 5 27
http://bbs.boingboing.net/t/x/24774 jandrese MarjaE 6 33
http://bbs.boingboing.net/t/x/21215 chenille bwv812 9 50
http://bbs.boingboing.net/t/x/8311 William_Holz Ignatius 5 29
http://bbs.boingboing.net/t/x/19819 AliceWeir fireshadow 6 35
http://bbs.boingboing.net/t/x/10798 Vallindsay2 technogeekagain 6 35
http://bbs.boingboing.net/t/x/11183 Space_Monkey Cowicide 7 41
http://bbs.boingboing.net/t/x/27759 aikimo milliefink 7 41
http://bbs.boingboing.net/t/x/11385 fireshadow codinghorror 10 60
http://bbs.boingboing.net/t/x/8076 Brainspore wanderandwonder 6 37
http://bbs.boingboing.net/t/x/19579 bwv812 hallam 5 31
http://bbs.boingboing.net/t/x/15946 SteampunkBanana Daneyul 5 31
http://bbs.boingboing.net/t/x/19579 hallam bwv812 5 31
http://bbs.boingboing.net/t/x/27533 aikimo Hyphen 8 50
http://bbs.boingboing.net/t/x/11996 timquinn rattypilgrim 6 38
http://bbs.boingboing.net/t/x/23651 NickyG rattypilgrim 5 32
http://bbs.boingboing.net/t/x/14456 Espresso Mongrove 7 45
http://bbs.boingboing.net/t/x/17112 Dave_Baxter bwv812 8 52
http://bbs.boingboing.net/t/x/1874 HannesAlfven rknop 9 59
http://bbs.boingboing.net/t/x/12534 Glitch teapot 10 66
http://bbs.boingboing.net/t/x/24774 MarjaE jandrese 5 33
(57 row(s) affected)
