Yeah, using Oracle here. The DATE 'YYYY-MM-DD' should be compatible, and the date part extraction was separated into a second CTE, for ease of modification. Although i used Dual, a CTE named Dual can be added on top to include one record and make it all work elsewhere (and in Oracle too.)

ROLLUP is great for reporting or (less frequently) generating records. I tried CUBE, but without a serious study, the month totals seemed better done in the second query, in line. FWIW, i was too lazy to (re-learn and) write a PIVOT query. :)

Unfortunately, i did not have enough polls with rep to check the correlation between rep and posts. Though, that avenue looks promising.

It'd be a lot of fun to write these queries against the actual database. That'd not only remove clerical errors, but be on all the data. By year, by month, and so on.

There is one other effect, perhaps (it's just a guess), worth looking into. That is, if polls are changed often, does that cause subsequent polls to have a higher rating? That is, if they are changed often, people might anticipate them, and thus be more excited when one shows up. That excitement could translate into more posts, or so goes my hypothesis. If true, the quality of the poll would be of lesser significance than the frequency, vis-a-vis the popularity of a poll.

In reply to Re^4: Terminal decline? (Oracle-2-PostgreSQL port) by chacham
in thread Terminal decline? by BrowserUk

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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, details, 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, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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.