Re^2: 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. :)

Comment on Re^2: Terminal decline? Select or Download Code

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]
Re^4: Terminal decline? (Oracle-2-PostgreSQL port) by chacham (Prior) on Jun 16, 2015 at 12:24 UTC
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]


"be consistent"
	PerlMonks