Post counts month ending 17 Oct 2014

[postgres@sofa ~]$ sql_tdwtf month_posts
# Post counts per user over last month
WITH LastMonth AS (
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id
    WHERE topic_id NOT IN (
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
    ) AND topic_id NOT IN (
       1000, 1673, 3125
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT row_number, count
FROM LastMonth, TotalUsers

 row_number | count
------------+-------
          1 |  1271
          2 |  1009
          3 |   972
          4 |   954
          5 |   829
          6 |   774
          7 |   545
          8 |   483
          9 |   464
         10 |   422
         11 |   407
         12 |   359
         13 |   337
         14 |   324
         15 |   321
         16 |   315
         17 |   292
         18 |   263
         19 |   253
         20 |   228
         21 |   210
         22 |   195
         23 |   175
         24 |   168
         25 |   148
         26 |   146
         27 |   127
         28 |   127
         29 |   117
         30 |   109
         31 |   107
         32 |   101
         33 |   101
         34 |    98
         35 |    97
         36 |    94
         37 |    91
         38 |    89
         39 |    86
         40 |    86
         41 |    85
         42 |    84
         43 |    79
         44 |    75
         45 |    74
         46 |    74
         47 |    74
         48 |    74
         49 |    74
         50 |    59
         51 |    58
         52 |    55
         53 |    52
         54 |    52
         55 |    50
         56 |    49
         57 |    47
         58 |    47
         59 |    46
         60 |    45
         61 |    45
         62 |    44
         63 |    44
         64 |    43
         65 |    43
         66 |    42
         67 |    39
         68 |    37
         69 |    34
         70 |    33
         71 |    31
         72 |    31
         73 |    31
         74 |    30
         75 |    30
         76 |    28
         77 |    27
         78 |    27
         79 |    27
         80 |    27
         81 |    27
         82 |    26
         83 |    25
         84 |    24
         85 |    23
         86 |    22
         87 |    22
         88 |    21
         89 |    20
         90 |    20
         91 |    19
         92 |    19
         93 |    18
         94 |    16
         95 |    16
         96 |    15
         97 |    15
         98 |    14
         99 |    14
        100 |    14
        101 |    13
        102 |    13
        103 |    12
        104 |    11
        105 |    11
        106 |    10
        107 |    10
        108 |    10
        109 |    10
        110 |    10
        111 |     9
        112 |     9
        113 |     9
        114 |     9
        115 |     9
        116 |     9
        117 |     8
        118 |     8
        119 |     8
        120 |     8
        121 |     8
        122 |     8
        123 |     8
        124 |     7
        125 |     7
        126 |     7
        127 |     7
        128 |     7
        129 |     7
        130 |     7
        131 |     7
        132 |     6
        133 |     6
        134 |     6
        135 |     5
        136 |     5
        137 |     5
        138 |     5
        139 |     5
        140 |     5
        141 |     5
        142 |     5
        143 |     5
        144 |     4
        145 |     4
        146 |     4
        147 |     4
        148 |     4
        149 |     4
        150 |     4
        151 |     4
        152 |     4
        153 |     4
        154 |     4
        155 |     4
        156 |     4
        157 |     4
        158 |     4
        159 |     4
        160 |     4
        161 |     4
        162 |     3
        163 |     3
        164 |     3
        165 |     3
        166 |     3
        167 |     3
        168 |     3
        169 |     3
        170 |     3
        171 |     3
        172 |     3
        173 |     3
        174 |     3
        175 |     3
        176 |     3
        177 |     3
        178 |     3
        179 |     3
        180 |     3
        181 |     3
        182 |     3
        183 |     2
        184 |     2
        185 |     2
        186 |     2
        187 |     2
        188 |     2
        189 |     2
        190 |     2
        191 |     2
        192 |     2
        193 |     2
        194 |     2
        195 |     2
        196 |     2
        197 |     2
        198 |     2
        199 |     2
        200 |     2
        201 |     2
        202 |     2
        203 |     2
        204 |     2
        205 |     2
        206 |     2
        207 |     2
        208 |     2
        209 |     2
        210 |     2
        211 |     2
        212 |     2
        213 |     2
        214 |     2
        215 |     2
        216 |     2
        217 |     2
        218 |     2
        219 |     2
        220 |     1
        221 |     1
        222 |     1
        223 |     1
        224 |     1
        225 |     1
        226 |     1
        227 |     1
        228 |     1
        229 |     1
        230 |     1
        231 |     1
        232 |     1
        233 |     1
        234 |     1
        235 |     1
        236 |     1
        237 |     1
        238 |     1
        239 |     1
        240 |     1
        241 |     1
        242 |     1
        243 |     1
        244 |     1
        245 |     1
        246 |     1
        247 |     1
        248 |     1
        249 |     1
        250 |     1
        251 |     1
        252 |     1
        253 |     1
        254 |     1
        255 |     1
        256 |     1
        257 |     1
        258 |     1
        259 |     1
        260 |     1
        261 |     1
        262 |     1
        263 |     1
        264 |     1
        265 |     1
        266 |     1
        267 |     1
        268 |     1
        269 |     1
        270 |     1
        271 |     1
        272 |     1
        273 |     1
        274 |     1
        275 |     1
        276 |     1
        277 |     1
        278 |     1
        279 |     1
        280 |     1
        281 |     1
        282 |     1
        283 |     1
        284 |     1
        285 |     1
        286 |     1
        287 |     1
        288 |     1
        289 |     1
        290 |     1
        291 |     1
        292 |     1
        293 |     1
        294 |     1
        295 |     1
        296 |     1
        297 |     1
        298 |     1
        299 |     1
        300 |     1
        301 |     1
        302 |     1
        303 |     1
        304 |     1
        305 |     1
        306 |     1
        307 |     1
        308 |     1
        309 |     1
        310 |     1
        311 |     1
        312 |     1
(312 rows)

Elapsed: 0.831s
Backup taken:  2014-10-14 10:45:30.353836
[postgres@sofa ~]$