Geplaatst 
Ter gelegenheid van het registreren van de 100.000e user is in ieder geval een blik statjes opengetrokken
In het eerste statje zie je de users die in de afgelopen jaren de meeste topics hebben gestart (total), regyears geeft aan hoe lang ze al geregistreerd zijn en 'topicsday' is het gemiddelde aantal nieuwe topics per dag. Vanzelfsprekend veel oudgedienden in dit statje.
- Code
+------+--------------+-------+----------+-----------+
| rank | username | total | regyears | topicsday |
+------+--------------+-------+----------+-----------+
| 1 | stef | 4468 | 7.92 | 1.55 |
| 2 | Cayenne | 4267 | 5.34 | 2.19 |
| 3 | Karin | 2533 | 7.92 | 0.88 |
| 4 | Mickey | 2291 | 7.79 | 0.81 |
| 5 | Nimber | 1876 | 7.92 | 0.65 |
| 6 | Maya | 1863 | 7.11 | 0.72 |
| 7 | Mireille | 1844 | 5.93 | 0.85 |
| 8 | lonneke | 1775 | 7.86 | 0.62 |
| 9 | LiSSy | 1753 | 7.48 | 0.64 |
| 10 | Sjoert | 1691 | 4.37 | 1.06 |
| 11 | Heike | 1655 | 7.92 | 0.57 |
| 12 | #Ymke# | 1613 | 7.92 | 0.56 |
| 13 | Eline | 1603 | 5.18 | 0.85 |
| 14 | BartVB | 1577 | 7.92 | 0.55 |
| 15 | Laui! | 1433 | 7.92 | 0.50 |
| 16 | Cody | 1413 | 7.65 | 0.51 |
| 17 | imker | 1341 | 7.12 | 0.52 |
| 18 | orri | 1287 | 7.92 | 0.45 |
| 19 | Sylvia | 1284 | 5.39 | 0.65 |
| 20 | Pink_Horizon | 1276 | 7.75 | 0.45 |
| 21 | Siempie | 1229 | 6.90 | 0.49 |
| 22 | Tancreda4 | 1203 | 5.91 | 0.56 |
| 23 | Sammie | 1202 | 5.52 | 0.60 |
| 24 | Nathalie | 1198 | 7.92 | 0.41 |
| 25 | Ethelate | 1197 | 7.19 | 0.46 |
| 26 | Charaz | 1192 | 6.67 | 0.49 |
| 27 | DubbelDun | 1190 | 7.67 | 0.43 |
| 28 | parouscha | 1182 | 6.64 | 0.49 |
| 29 | Lielle | 1173 | 7.91 | 0.41 |
| 30 | Sjimmy | 1149 | 7.76 | 0.41 |
| 31 | Oscar | 1149 | 7.22 | 0.44 |
| 32 | Sirius_ | 1143 | 7.92 | 0.40 |
| 33 | Nymphadora | 1125 | 5.27 | 0.59 |
| 34 | Apple | 1105 | 6.87 | 0.44 |
| 35 | Diana_Mabel | 1097 | 6.85 | 0.44 |
| 36 | Banjer | 1068 | 6.51 | 0.45 |
| 37 | Laura | 1063 | 7.81 | 0.37 |
| 38 | Bellmaid | 1047 | 7.25 | 0.40 |
| 39 | Anna-Jelly | 1029 | 7.29 | 0.39 |
| 40 | pharagirlke | 1022 | 4.79 | 0.58 |
+------+--------------+-------+----------+-----------+
SET @i=0; SELECT @i:=@i+1 as rank, username, count(*) AS total, round((unix_timestamp()-user_regdate)/(3600*24*365), 2) AS regyears, round(count(*)/((unix_timestamp()-user_regdate)/(3600*24)), 2) AS topicsday FROM topics t JOIN users u ON (t.topic_poster=u.user_id) GROUP BY t.topic_poster ORDER BY total DESC LIMIT 40;
Mensen met de meest bezienswaardige topics (alleen users met meer dan 50 topics op hun naam). Bepalend is het gemiddelde keer dat hun topics wordt bekeken. De topics van GinnyBanshee op HAW doen het altijd erg goed
- Code
+------+--------------+------------+--------+
| rank | username | topicviews | topics |
+------+--------------+------------+--------+
| 1 | joelledsr | 3519 | 87 |
| 2 | 0mandy0 | 3328 | 51 |
| 3 | GinnyBanshee | 3285 | 141 |
| 4 | Kel2004 | 3280 | 62 |
| 5 | Pannenkoek | 3173 | 63 |
| 6 | woldhoeve | 3030 | 87 |
| 7 | Wranglerbutt | 3010 | 61 |
| 8 | Tinkerloos | 2875 | 188 |
| 9 | Jetske_ | 2724 | 61 |
| 10 | milou123 | 2716 | 66 |
| 11 | sammy1 | 2590 | 66 |
| 12 | mariska32 | 2547 | 66 |
| 13 | slimmerik | 2536 | 55 |
| 14 | Karin_88 | 2437 | 51 |
| 15 | Hitime | 2435 | 65 |
| 16 | KG_Images | 2403 | 172 |
| 17 | MissHIS | 2249 | 78 |
| 18 | MamaMiesje | 2223 | 84 |
| 19 | Caye | 2204 | 117 |
| 20 | Lontje | 2202 | 83 |
| 21 | Odess | 2191 | 55 |
| 22 | Sannah | 2170 | 130 |
| 23 | Lolleke | 2162 | 71 |
| 24 | Eliine | 2162 | 57 |
| 25 | rowo | 2150 | 71 |
| 26 | Balte | 2139 | 51 |
| 27 | risje | 2131 | 57 |
| 28 | Meetje22 | 2113 | 197 |
| 29 | Needje | 2079 | 141 |
| 30 | MissJ | 2059 | 124 |
| 31 | _Nikki_ | 2049 | 62 |
| 32 | savath | 2023 | 65 |
| 33 | Kimmes | 2014 | 186 |
| 34 | olijf | 1997 | 54 |
| 35 | Zahlina | 1985 | 74 |
| 36 | WiskyBokjes | 1984 | 147 |
| 37 | Lotte_ | 1966 | 54 |
| 38 | Rock | 1940 | 86 |
| 39 | Zooist | 1940 | 97 |
| 40 | Cyntje | 1927 | 232 |
+------+--------------+------------+--------+
set @i=0; SELECT @i:=@i+1 as rank, username, round(avg(topic_views)) as topicviews, count(*) as topics FROM topics JOIN users ON (topic_poster=user_id) GROUP BY topic_poster having topics > 50 ORDER BY topicviews DESC LIMIT 40;
Een grafiekje dit keer, hier met het aantal nieuwsberichten op VN, het aantal reacties en het aantal gebruikers dat reageert per maand over de afgelopen jaren:
- Code
+------+-------------+------------+--------+-------+
| rank | username | user_posts | total | no_hk |
+------+-------------+------------+--------+-------+
| 1 | runningkawa | 67022 | 73774 | 65498 |
| 2 | Maya | 68609 | 92488 | 64105 |
| 3 | Lielle | 47660 | 56719 | 49443 |
| 4 | Karin | 50993 | 82301 | 49192 |
| 5 | Cassidy | 48189 | 66032 | 48145 |
| 6 | Oscar | 47255 | 48381 | 47715 |
| 7 | Eline | 44051 | 46411 | 44244 |
| 8 | DubbelDun | 43040 | 68340 | 43017 |
| 9 | Apple | 46961 | 66322 | 39120 |
| 10 | KiWiKo | 36982 | 47286 | 36985 |
| 11 | Nymphadora | 33653 | 92765 | 35303 |
| 12 | Cayenne | 28801 | 49667 | 33157 |
| 13 | Mireille | 32887 | 34725 | 32929 |
| 14 | Sjoert | 28755 | 61515 | 30059 |
| 15 | Robin__ | 20450 | 101427 | 28726 |
| 16 | Mickey | 31426 | 36374 | 28127 |
| 17 | stef | 27362 | 29090 | 27966 |
| 18 | Neonlight | 26501 | 32613 | 27150 |
| 19 | Bellatrix | 26641 | 66643 | 26976 |
| 20 | Sizzy | 26405 | 29270 | 26890 |
| 21 | Desiree | 26679 | 27133 | 26390 |
| 22 | Spirit | 22064 | 66747 | 25671 |
| 23 | Sonja | 24383 | 32323 | 25043 |
| 24 | Sammie | 23879 | 27217 | 24804 |
| 25 | Scum | 25400 | 34128 | 24679 |
| 26 | Urielle | 24681 | 31469 | 24518 |
| 27 | BartVB | 23797 | 23931 | 23689 |
| 28 | Sylvia | 26737 | 44068 | 23673 |
| 29 | Katja | 22711 | 33459 | 23414 |
| 30 | Olympia | 22777 | 38593 | 23112 |
| 31 | Nimber | 22239 | 33640 | 22997 |
| 32 | Sita_ | 14138 | 56405 | 22560 |
| 33 | Justy | 29926 | 58683 | 22532 |
| 34 | MarijeR | 20782 | 24867 | 22446 |
| 35 | snowdon | 18335 | 34140 | 22373 |
| 36 | Nathalie | 22741 | 26172 | 22230 |
| 37 | MoonChloe | 21503 | 24816 | 21259 |
| 38 | ellepel | 20513 | 26933 | 20733 |
| 39 | Lovely | 21195 | 29765 | 20697 |
| 40 | Escha | 18091 | 33659 | 20651 |
+------+-------------+------------+--------+-------+
De drukste forums in 2001 en 2008 vergeleken, gaat hier om het aantal posts dat geplaatst is in november. Eerst 2001:
- Code
+--------------------------------------+-------+
| forum_name | posts |
+--------------------------------------+-------+
| [PP] Paardenpraat | 9693 |
| [LZP] Het leven zonder paarden | 7555 |
| [RT-HAW] Horses At Work | 6654 |
| [KH] 't Kippenhok | 6008 |
| [VSW] Verzorging, Stalling en Weide. | 5919 |
| [JH] Jeugdhonk | 3662 |
| [BM] Boktmeetings | 2439 |
| [EA-TK-PP] Te Koop: Paarden, Pony's | 2227 |
| [F-AM] Algemeen | 1977 |
| [WN-AM] Western Algemeen | 1185 |
| [RT-P] Praktijkproblemen | 1108 |
| [MD] Medisch | 843 |
| [LN] Links | 585 |
| [AR] Aangespannen rijden | 568 |
| [VT] Voedertijd | 51 |
| [FZ] Forumzaken | 24 |
| [VS] Voeding en supplementen | 6 |
| [WD-V] Vragen en discussies | 4 |
+--------------------------------------+-------+
En dat zijn meteen alle forums, wel met de huidige forumnamen
Hieronder hetzelfde overzicht maar dan voor 2008:- Code
+----------------------------------------+--------+
| forum_name | posts |
+----------------------------------------+--------+
| [HK] de Huiskamer | 444628 |
| [JHK] Huiskamer | 82569 |
| [MHK] De Manegehuiskamer | 31712 |
| [LZP] Het leven zonder paarden | 26487 |
| [SK] De Speelkamer | 20895 |
| [EA-TK-AP] Te Koop: Alles voor Paarden | 14198 |
| [ENT-HK] Entertainment-Huiskamer | 12774 |
| [MW] Mensenwelzijn | 9952 |
| [BB] BeestenBoel | 7734 |
| [ITP] In The Picture | 7367 |
| [PP] Paardenpraat | 7212 |
| [VN] Voorpagina Nieuws | 7154 |
| [ENT] Entertainment | 6899 |
| [TI] Tinkers | 6235 |
| [BM] Boktmeetings | 5521 |
| [VT] Voedertijd | 5381 |
| [STW] School, studie en werk | 5342 |
| [HAR] Harnachement | 5131 |
| [RT-P] Praktijkproblemen | 5117 |
+----------------------------------------+--------+
set @start=unix_timestamp(date('2008-11-01 00:00')); set @end=unix_timestamp(date('2008-12-01')); SELECT forum_name, posts from (select forum_id, count(*) as posts from posts where post_time between @start and @end group by forum_id) as pc join forums using (forum_id) order by posts desc limit 20;
En hetzelfde maar dan voor posts van gebruikers in 2001 en 2008.
- Code
+------+--------------+-------+
| rank | username | posts |
+------+--------------+-------+
| 1 | Karin | 936 |
| 2 | Yentl | 910 |
| 3 | Dorée | 839 |
| 4 | marije b | 776 |
| 5 | Nathalie | 719 |
| 6 | BartVB | 705 |
| 7 | Jura | 639 |
| 8 | Waventy | 623 |
| 9 | Sunnylover | 612 |
| 10 | Prisca | 586 |
| 11 | Anna-Jelly | 539 |
| 12 | Nimber | 518 |
| 13 | Pink_Horizon | 514 |
| 14 | Mytens | 507 |
| 15 | Margreet | 489 |
| 16 | Valent | 484 |
| 17 | PUCK | 474 |
| 18 | stef | 470 |
| 19 | assie | 452 |
| 20 | Cody | 413 |
| 21 | marfiek | 408 |
| 22 | orri | 401 |
| 23 | party-girl | 399 |
| 24 | Felice | 395 |
| 25 | Joleen | 393 |
| 26 | Spirit | 384 |
| 27 | Maaike | 379 |
| 28 | Desiree | 374 |
| 29 | Macario | 370 |
| 30 | Marjolein | 367 |
+------+--------------+-------+
En 2008, hier gesorteerd op posts die niet in de HK geplaatst zijn:
- Code
+------+--------------+-------+-------+
| rank | username | total | no_hk |
+------+--------------+-------+-------+
| 1 | Maya | 3470 | 2659 |
| 2 | suleilitha | 2053 | 1780 |
| 3 | Katja | 3267 | 1746 |
| 4 | Nicole_ | 1638 | 1215 |
| 5 | Berdien | 1312 | 1102 |
| 6 | KiWiKo | 1204 | 1090 |
| 7 | Neonlight | 1440 | 1085 |
| 8 | MoniekJ | 1652 | 1069 |
| 9 | Cayenne | 1077 | 1001 |
| 10 | Fiffill | 2594 | 888 |
| 11 | Oscar | 852 | 851 |
| 12 | Keedie | 1354 | 829 |
| 13 | A_lette | 1264 | 828 |
| 14 | Sita_ | 2209 | 799 |
| 15 | Mas | 802 | 783 |
| 16 | Zwartwitje | 844 | 729 |
| 17 | Karin | 1322 | 723 |
| 18 | Bellatrix | 2821 | 720 |
| 19 | detour | 1123 | 689 |
| 20 | Sara_N | 1611 | 687 |
| 21 | Apple | 1276 | 686 |
| 22 | Magnummm | 1117 | 663 |
| 23 | Jester_ | 1815 | 655 |
| 24 | Unwritten | 2316 | 654 |
| 25 | lifaya | 653 | 644 |
| 26 | Eline | 857 | 628 |
| 27 | katiavl | 1900 | 618 |
| 28 | Djilan | 943 | 614 |
| 29 | Lizzy_Bennie | 682 | 597 |
| 30 | _janet_ | 799 | 596 |
+------+--------------+-------+-------+
En gesorteerd op totaal aantal posts, inclusief huiskamer:
- Code
+------+--------------+-------+-------+
| rank | username | total | no_hk |
+------+--------------+-------+-------+
| 1 | loveginger | 4561 | 310 |
| 2 | Maya | 3470 | 2659 |
| 3 | Robin__ | 3297 | 467 |
| 4 | Katja | 3267 | 1746 |
| 5 | myxlea | 3096 | 194 |
| 6 | x_Rebecca | 2933 | 133 |
| 7 | Lontje | 2881 | 110 |
| 8 | Bellatrix | 2821 | 720 |
| 9 | Fiffill | 2594 | 888 |
| 10 | Unwritten | 2316 | 654 |
| 11 | Tass | 2301 | 114 |
| 12 | SteffiiY | 2298 | 375 |
| 13 | Sita_ | 2209 | 799 |
| 14 | Babsie_Flash | 2154 | 103 |
| 15 | Yffiejj | 2105 | 185 |
| 16 | Lon_ivo | 2102 | 63 |
| 17 | suleilitha | 2053 | 1780 |
| 18 | Liedje | 2049 | 61 |
| 19 | Kimm__ | 2019 | 331 |
| 20 | Zinn | 1990 | 81 |
| 21 | xLies | 1963 | 96 |
| 22 | sjakie | 1915 | 55 |
| 23 | LovingTweety | 1904 | 205 |
| 24 | katiavl | 1900 | 618 |
| 25 | pluklief | 1897 | 110 |
| 26 | Pistasche | 1827 | 102 |
| 27 | mirjamalvaro | 1819 | 331 |
| 28 | Jester_ | 1815 | 655 |
| 29 | Dreeetje | 1813 | 183 |
| 30 | Ing__ | 1793 | 54 |
+------+--------------+-------+-------+
set @start=unix_timestamp(date('2008-11-01 00:00')); set @end=unix_timestamp(date('2008-12-01')); SET @i:=0; SELECT @i:=@i+1 as rank, username, total, no_hk FROM (SELECT poster_id, count(*) as total, sum(if((forum_id = 34 OR forum_id = 146 OR forum_id = 160), 0, 1)) as no_hk FROM posts WHERE post_time between @start and @end GROUP BY poster_id ORDER BY no_hk DESC LIMIT 30) p JOIN users u ON (poster_id=user_id) ORDER BY no_hk DESC;
Lijkt me duidelijk dat Maya in november 2008 de onbetwiste queen of posts is
? 
