in reply to Re: Terminal decline?
in thread Terminal decline?

Polls are getting poorer (all the best ones have been done)

After giggling, /me wonders why this is so. Is it that there are not enough suggestions, or that the current pollsters have different tastes. If the vintage of the poll had anything to do with it, there are still plenty of unused poll suggestions from the golden era lying around.

Perhaps this is due to hindsight. If more people reply to a poll, it is retroactively a good poll because it promoted a good (interesting or fun) discussion. For example, In this poll, I. The more serious polls, perhaps, even have less replies.

Personally, i think the quality of the poll is not as important as how often it is changed. Keep it fresh.

Anyway, let's check 2004, 2005, 2014, and 2015:

-- Put together manually.
-- Could not see rep for all polls
-- Replies counted in different ways. Should really be double checked.
-- ANSI date format used to aid working in another RDBMS
-- 12/31 counted in following year.
WITH
    Poll(Posted, Votes, Replies, Rep, Id)
AS
    (
     SELECT DATE '2013-12-31', 0367, 014, NULL, 1068839 FROM Dual UNIO
+N ALL
     SELECT DATE '2014-02-01', 0471, 014, NULL, 1072962 FROM Dual UNIO
+N ALL
     SELECT DATE '2014-03-01', 0617, 012, 15, 1076665 FROM Dual UNION 
+ALL
     SELECT DATE '2014-04-01', 0685, 010, 21, 1080513 FROM Dual UNION 
+ALL
     SELECT DATE '2014-05-01', 0790, 052, 13, 1084594 FROM Dual UNION 
+ALL
     SELECT DATE '2014-06-01', 0641, 027, 18, 1088179 FROM Dual UNION 
+ALL
     SELECT DATE '2014-07-01', 0304, 023, 13, 1091827 FROM Dual UNION 
+ALL
     SELECT DATE '2014-07-16', 0261, 039, 15, 1093839 FROM Dual UNION 
+ALL
     SELECT DATE '2014-08-01', 0381, 024, 19, 1095870 FROM Dual UNION 
+ALL
     SELECT DATE '2014-08-16', 0300, 038, 19, 1097658 FROM Dual UNION 
+ALL
     SELECT DATE '2014-09-01', 0158, 026, 05, 1099153 FROM Dual UNION 
+ALL
     SELECT DATE '2014-09-16', 0395, 026, 16, 1100721 FROM Dual UNION 
+ALL
     SELECT DATE '2014-10-01', 0385, 032, 16, 1102521 FROM Dual UNION 
+ALL
     SELECT DATE '2014-10-16', 0230, 032, 12, 1104030 FROM Dual UNION 
+ALL
     SELECT DATE '2014-11-01', 0260, 035, 21, 1105789 FROM Dual UNION 
+ALL
     SELECT DATE '2014-11-17', 0214, 012, 19, 1107394 FROM Dual UNION 
+ALL
     SELECT DATE '2014-12-01', 0406, 014, 14, 1108806 FROM Dual UNION 
+ALL
     SELECT DATE '2014-12-17', 0244, 018, 16, 1110616 FROM Dual UNION 
+ALL
     SELECT DATE '2015-01-01', 0306, 013, 13, 1111889 FROM Dual UNION 
+ALL
     SELECT DATE '2015-01-16', 0263, 023, 25, 1113517 FROM Dual UNION 
+ALL
     SELECT DATE '2015-02-01', 0472, 025, 21, 1115220 FROM Dual UNION 
+ALL
     SELECT DATE '2015-03-01', 0681, 020, 15, 1118273 FROM Dual UNION 
+ALL
     SELECT DATE '2015-04-01', 0579, 028, 14, 1122103 FROM Dual UNION 
+ALL
     SELECT DATE '2015-05-01', 0610, 018, 10, 1125345 FROM Dual UNION 
+ALL
     SELECT DATE '2015-06-01', 0406, 028, 11, 1128535 FROM Dual UNION 
+ALL
     SELECT DATE '2003-12-31', 0381, 038, NULL,  317976 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-01-10', 0554, 073, NULL,  320300 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-01-31', 0493, 043, NULL,  325590 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-02-13', 0231, 040, NULL,  328924 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-02-20', 0528, 038, NULL,  330663 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-03-14', 0674, 035, NULL,  336465 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-03-28', 0447, 025, NULL,  340356 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-04-06', 0685, 035, NULL,  343106 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-04-27', 0354, 047, NULL,  348710 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-05-06', 0239, 016, NULL,  351331 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-05-09', 1134, 055, NULL,  351805 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-05-27', 0750, 092, NULL,  357125 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-06-16', 0676, 032, NULL,  367278 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-07-14', 0847, 096, NULL,  374488 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-08-02', 0331, 040, NULL,  379195 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-08-16', 0721, 048, NULL,  383208 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-08-31', 1208, 097, NULL,  387377 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-09-27', 0627, 067, NULL,  394350 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-10-19', 0570, 047, NULL,  400428 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-11-02', 0384, 047, NULL,  404708 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-11-08', 0719, 039, NULL,  405984 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-11-17', 0603, 031, NULL,  408565 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-12-15', 0385, 025, NULL,  415282 FROM Dual UNIO
+N ALL
     SELECT DATE '2004-12-31', 0516, 037, NULL,  418471 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-01-15', 0975, 034, NULL,  422479 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-02-15', 0941, 076, NULL,  431048 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-03-16', 0702, 088, NULL,  439956 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-04-09', 0359, 022, NULL,  446224 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-04-23', 0304, 019, NULL,  450653 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-05-05', 0526, 051, NULL,  454408 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-05-27', 0383, 069, NULL,  461272 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-06-08', 0919, 044, NULL,  464548 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-07-12', 0462, 029, NULL,  474189 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-07-21', 0152, 027, NULL,  476741 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-07-27', 0275, 021, NULL,  478760 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-08-04', 0293, 028, NULL,  480707 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-08-10', 0288, 035, NULL,  482550 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-08-17', 0249, 044, NULL,  484367 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-08-24', 0194, 004, NULL,  486258 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-09-06', 0141, 048, NULL,  489623 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-09-08', 0294, 056, NULL,  490121 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-09-13', 0427, 048, NULL,  491469 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-09-22', 0270, 023, NULL,  494181 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-09-29', 0478, 034, NULL,  496105 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-10-09', 0248, 028, NULL,  498505 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-10-12', 0560, 029, NULL,  499711 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-10-30', 0122, 031, NULL,  504009 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-11-01', 0096, 011, NULL,  504562 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-11-02', 0310, 022, NULL,  504834 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-11-08', 0205, 032, NULL,  506825 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-11-16', 0646, 106, NULL,  509130 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-12-06', 0358, 052, NULL,  514544 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-12-14', 0291, 024, NULL,  516577 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-12-20', 0263, 031, NULL,  518012 FROM Dual UNIO
+N ALL
     SELECT DATE '2005-12-26', 0293, 031, NULL,  519073 FROM Dual
    ),
    Split
AS
    (
     SELECT
        Posted,
        TO_CHAR(Posted, 'YY')
        + CASE
         WHEN TO_CHAR(Posted, 'MM') = 12 AND TO_CHAR(Posted, 'DD') = 3
+1  THEN 1
         ELSE 0
        END Y, 
        CASE
         WHEN TO_CHAR(Posted, 'MM') = 12 AND TO_CHAR(Posted, 'DD') = 3
+1  THEN '01'
         ELSE TO_CHAR(Posted, 'MM')
        END M,
        Votes,
        Replies,
        Rep,
        Id
     FROM
        Poll
    )
SELECT
    Y,
    M,
    ROUND(AVG(Votes)) Votes,
    ROUND(AVG(Replies)) Replies
FROM
    Split
GROUP BY
    ROLLUP(Y, M)
ORDER BY
    Y,
    M;

[download]
The results are:
Y M VOTES REPLIES
4 01 476 51
4 02 380 39
4 03 561 30
4 04 520 41
4 05 708 54
4 06 676 32
4 07 847 96
4 08 753 62
4 09 627 67
4 10 570 47
4 11 569 39
4 12 385 25
4   589 48
5 01 746 36
5 02 941 76
5 03 702 88
5 04 332 21
5 05 455 60
5 06 919 44
5 07 296 26
5 08 256 28
5 09 322 42
5 10 310 29
5 11 314 43
5 12 301 35
5   392 39
14 01 367 14
14 02 471 14
14 03 617 12
14 04 685 10
14 05 790 52
14 06 641 27
14 07 283 31
14 08 341 31
14 09 277 26
14 10 308 32
14 11 237 24
14 12 325 16
14   395 25
15 01 285 18
15 02 472 25
15 03 681 20
15 04 579 28
15 05 610 18
15 06 406 28
15   474 22
    456 37

By year, average replies have gone down a little (48 and 39 to 25 and 37), and votes (589 and 392 to 395 474) are more interesting, with more average votes in 2015 (so far) than 2005.

By month:

--same CTEs, different main query
SELECT
    Say.M,
    ROUND(AVG(Say.Votes)) Votes,
    ROUND(AVG(CASE Split.Y WHEN 04 THEN  Split.Votes END)) V_04,
    ROUND(AVG(CASE Split.Y WHEN 05 THEN  Split.Votes END)) V_05,
    ROUND(AVG(CASE Split.Y WHEN 14 THEN  Split.Votes END)) V_14,
    ROUND(AVG(CASE Split.Y WHEN 15 THEN  Split.Votes END)) V_15,
    ROUND(AVG(Say.Replies)) Replies,
    ROUND(AVG(CASE Split.Y WHEN 04 THEN  Split.Replies END)) R_04,
    ROUND(AVG(CASE Split.Y WHEN 05 THEN  Split.Replies END)) R_05,
    ROUND(AVG(CASE Split.Y WHEN 14 THEN  Split.Replies END)) R_14,
    ROUND(AVG(CASE Split.Y WHEN 15 THEN  Split.Replies END)) R_15
FROM
    Split,
    Split Say --Split All Years
WHERE
    Say.M = Split.M
GROUP BY
    Say.M
ORDER BY
    Say.M;

[download]
M VOTES V_04 V_05 V_14 V_15 REPLIES R_04 R_05 R_14 R_15
01 482 476 746 367 285 34 51 36 14 18
02 529 380 941 471 472 39 39 76 14 25
03 624 561 702 617 681 36 30 88 12 20
04 494 520 332 685 579 27 41 21 10 28
05 633 708 455 790 610 50 54 60 52 18
06 661 676 919 641 406 33 32 44 27 28
07 384 847 296 283   39 96 26 31  
08 441 753 256 341   40 62 28 31  
09 349 627 322 277   41 67 42 26  
10 353 570 310 308   33 47 29 32  
11 382 569 314 237   37 39 43 24  
12 320 385 301 325   28 25 35 16  

Here, it varies, with no champion, either.

Perhaps the major contender here is the frequency.

SELECT
    Y,
    M,
    COUNT(*) Total
FROM
    Split
GROUP BY
    ROLLUP(Y, M)
ORDER BY
    Y,
    M;

[download]
Y M TOTAL
4 01 3
4 02 2
4 03 2
4 04 2
4 05 3
4 06 1
4 07 1
4 08 3
4 09 1
4 10 1
4 11 3
4 12 1
4   23
5 01 2
5 02 1
5 03 1
5 04 2
5 05 2
5 06 1
5 07 3
5 08 4
5 09 5
5 10 3
5 11 4
5 12 4
5   32
14 01 1
14 02 1
14 03 1
14 04 1
14 05 1
14 06 1
14 07 2
14 08 2
14 09 2
14 10 2
14 11 2
14 12 2
14   18
15 01 2
15 02 1
15 03 1
15 04 1
15 05 1
15 06 1
15   7
    80

It's gone down a little, making it pique our interest less.

That took way too long to put together. :)

Replies are listed 'Best First'.
Re^3: Terminal decline? (Oracle-2-PostgreSQL port)
by erix (Prior) on Jun 15, 2015 at 20:56 UTC

    That took way too long to put together. :)

    Nice!

    Now it was easy for me to port to postgres (and it had to be 9.5devel as only in that (not-yet-released) version does rollup exist).

    (From the combination of dual and rollup I think I can conclude you concocted the above SQL on Oracle)

    (PostgreSQL 9.5 development is here: git://git.postgresql.org/git/postgresql.git ((it's pretty stable, always, and especially now that it is in feature-freeze (but NOT(!) for production of course)).

    I formatted it a bit to my and postgres' taste and I wrapped the whole thing in some bash tomfoolery to run the three CTE main-selects consecutively:

    Read more... (13 kB)
[reply]
[d/l]
[select]

      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.

[reply]

In Section Perl Monks Discussion