posts_read5

[postgres@sofa ~]$ sql_tdwtf posts_read5
# Another attempt
WITH exclusions AS ( /* Which categories to exclude from counters */
        SELECT user_id, id, topic_id, post_number
        FROM posts
        WHERE raw LIKE '%[UUID removed to prevent this thread accidentally being marked as excluded]%' AND
        user_id IN  (
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                        SELECT g.id
                        FROM groups g
                        WHERE g.name IN ('admins')
                )
        )
),
posts_read AS (
        SELECT u.username, u.id user_id, count(*), log(2, count(*)) ln2, log(3.0, count(*)) ln3
        FROM post_timings pt
        JOIN users u on u.id=pt.user_id
        WHERE pt.topic_id NOT IN (
                SELECT topic_id
                FROM exclusions
        )
        GROUP BY u.username, u.id
)
SELECT * from posts_read
WHERE ln3 >= 10 and ln3 <11
ORDER BY ln3 DESC


    username     | user_id | count  |         ln2         |         ln3
-----------------+---------+--------+---------------------+---------------------
 accalia         |     671 | 167974 | 17.3578784159293733 | 10.9516019514856395
 PJH             |      20 | 167583 | 17.3545162806361869 | 10.9494806802936355
 boomzilla       |      18 | 166476 | 17.3449546807021560 | 10.9434479824035085
 VinDuv          |     602 | 165777 | 17.3388843347826688 | 10.9396180205484330
 darkmatter      |     564 | 164207 | 17.3251561036842609 | 10.9309564710845424
 sockbot         |    1223 | 164017 | 17.3234858291864382 | 10.9299026452072345
 dkf             |      33 | 157239 | 17.2625995681675460 | 10.8914876915466974
 NetBot          |    1862 | 156439 | 17.2552406935565648 | 10.8868447586018277
 Zoidberg        |    1407 | 156350 | 17.2544196936997651 | 10.8863267653644949
 RaceProUK       |     294 | 152131 | 17.2149546379480076 | 10.8614270874643547
 HardwareGeek    |     123 | 148882 | 17.1838098154590159 | 10.8417768922863484
 antiquarian     |     598 | 146010 | 17.1557076548870207 | 10.8240464030418340
 Luhmann         |     628 | 144985 | 17.1455441226423809 | 10.8176339281473078
 Yamikuronue     |     788 | 144176 | 17.1374715033519602 | 10.8125406724477265
 ChaosTheEternal |     589 | 143950 | 17.1352082633381592 | 10.8111127269835459
 Maciejasjmj     |     261 | 142367 | 17.1192552495775926 | 10.8010474959428696
 loopback0       |     606 | 136547 | 17.0590380922187564 | 10.7630546996896854
 tarunik         |    1096 | 134744 | 17.0398615072441489 | 10.7509556216573142
 Jaloopa         |     587 | 131298 | 17.0024854149474474 | 10.7273739329550923
 blakeyrat       |      17 | 123369 | 16.9126203959514205 | 10.6706754186652347
 Keith           |     617 | 121943 | 16.8958474186706409 | 10.6600928482428127
 aliceif         |     922 | 117942 | 16.8477180383999440 | 10.6297265902090750
 mott555         |      69 | 117137 | 16.8378373255544556 | 10.6234925544883606
 FrostCat        |     110 | 109960 | 16.7466192864003156 | 10.5659403795235675
 ben_lubar       |     558 | 109471 | 16.7401892100406429 | 10.5618834530305136
 Arantor         |     762 | 109052 | 16.7346567035041756 | 10.5583928300448277
 abarker         |     603 | 105367 | 16.6850635729971650 | 10.5271030484352024
 Spencer         |     697 | 104947 | 16.6793014009812850 | 10.5234675226651869
 chubertdev      |      43 | 104476 | 16.6728120420288058 | 10.5193731930204625
 locallunatic    |      83 | 102990 | 16.6521447375657751 | 10.5063335757086163
 OffByOne        |    1163 |  96225 | 16.5541241455855634 | 10.4444894677656129
 Buddy           |     676 |  93609 | 16.5143596233663019 | 10.4194008475609275
 hungrier        |     681 |  92437 | 16.4961828181157581 | 10.4079325603034855
 cartman82       |     817 |  90678 | 16.4684649508012656 | 10.3904445331092264
 ookami          |     616 |  85810 | 16.3888581637514595 | 10.3402182425732768
 Zecc            |      30 |  82215 | 16.3271140149571633 | 10.3012620219900121
 PleegWat        |     997 |  82134 | 16.3256919398879652 | 10.3003647925170428
 Kuro            |     785 |  80658 | 16.2995300125948177 | 10.2838584541770228
 Onyx            |     579 |  80494 | 16.2965936287040138 | 10.2820058022124068
 CodingHorrorBot |    1253 |  79045 | 16.2703865871888297 | 10.2654709999673947
 ben_lubot       |    1318 |  78217 | 16.2551945823535843 | 10.2558859121004369
 antipattern     |     619 |  77177 | 16.2358833445319912 | 10.2437018775804994
 M_Adams         |     622 |  72132 | 16.1383518054427747 | 10.1821663276574951
 Polygeekery     |     824 |  70588 | 16.1071353249161439 | 10.1624709212914597
 cvi             |     588 |  68534 | 16.0645322721776405 | 10.1355913877257640
 another_sam     |     914 |  67161 | 16.0353360911272552 | 10.1171706484104167
 riking          |     561 |  65668 | 16.0029028964002399 | 10.0967075808537658
 ijij            |     958 |  65660 | 16.0027271294603430 | 10.0965966842616907
 JBert           |    1004 |  63635 | 15.9575328626321495 | 10.0680822766289359
 jaming          |    1070 |  63210 | 15.9478651944258247 | 10.0619826571099087
 izzion          |    1094 |  61444 | 15.9069845180095731 | 10.0361898220127686
 reverendryan    |     565 |  61235 | 15.9020688673235298 | 10.0330883917367800
(52 rows)

Elapsed: 15.148s
Backup taken:  2015-02-14 03:59:29.758072
[postgres@sofa ~]$