#!/bin/sh

mainsql[1]=''
mainsql[2]=''
mainsql[3]=''

for num in 1 2 3;
do

mainsql[${num}]='--'

echo "
-- Put together manually.  by chacham
-- 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 (
  -- cast values of (only) the first row to get datatypes right:
  select 
         cast('2013-12-31' as date   )
       , cast( 0367        as integer)
       , cast( 014         as integer)
       , cast( NULL        as integer)
       , cast(1068839      as integer)
                                                      union all

  select '2014-02-01', 0471, 014, NULL, 1072962  union all
  select '2014-03-01', 0617, 012,   15, 1076665  union all
  select '2014-04-01', 0685, 010,   21, 1080513  union all
  select '2014-05-01', 0790, 052,   13, 1084594  union all
  select '2014-06-01', 0641, 027,   18, 1088179  union all
  select '2014-07-01', 0304, 023,   13, 1091827  union all
  select '2014-07-16', 0261, 039,   15, 1093839  union all
  select '2014-08-01', 0381, 024,   19, 1095870  union all
  select '2014-08-16', 0300, 038,   19, 1097658  union all
  select '2014-09-01', 0158, 026,   05, 1099153  union all
  select '2014-09-16', 0395, 026,   16, 1100721  union all
  select '2014-10-01', 0385, 032,   16, 1102521  union all
  select '2014-10-16', 0230, 032,   12, 1104030  union all
  select '2014-11-01', 0260, 035,   21, 1105789  union all
  select '2014-11-17', 0214, 012,   19, 1107394  union all
  select '2014-12-01', 0406, 014,   14, 1108806  union all
  select '2014-12-17', 0244, 018,   16, 1110616  union all
  select '2015-01-01', 0306, 013,   13, 1111889  union all
  select '2015-01-16', 0263, 023,   25, 1113517  union all
  select '2015-02-01', 0472, 025,   21, 1115220  union all
  select '2015-03-01', 0681, 020,   15, 1118273  union all
  select '2015-04-01', 0579, 028,   14, 1122103  union all
  select '2015-05-01', 0610, 018,   10, 1125345  union all
  select '2015-06-01', 0406, 028,   11, 1128535  union all
  select '2003-12-31', 0381, 038, NULL,  317976  union all
  select '2004-01-10', 0554, 073, NULL,  320300  union all
  select '2004-01-31', 0493, 043, NULL,  325590  union all
  select '2004-02-13', 0231, 040, NULL,  328924  union all
  select '2004-02-20', 0528, 038, NULL,  330663  union all
  select '2004-03-14', 0674, 035, NULL,  336465  union all
  select '2004-03-28', 0447, 025, NULL,  340356  union all
  select '2004-04-06', 0685, 035, NULL,  343106  union all
  select '2004-04-27', 0354, 047, NULL,  348710  union all
  select '2004-05-06', 0239, 016, NULL,  351331  union all
  select '2004-05-09', 1134, 055, NULL,  351805  union all
  select '2004-05-27', 0750, 092, NULL,  357125  union all
  select '2004-06-16', 0676, 032, NULL,  367278  union all
  select '2004-07-14', 0847, 096, NULL,  374488  union all
  select '2004-08-02', 0331, 040, NULL,  379195  union all
  select '2004-08-16', 0721, 048, NULL,  383208  union all
  select '2004-08-31', 1208, 097, NULL,  387377  union all
  select '2004-09-27', 0627, 067, NULL,  394350  union all
  select '2004-10-19', 0570, 047, NULL,  400428  union all
  select '2004-11-02', 0384, 047, NULL,  404708  union all
  select '2004-11-08', 0719, 039, NULL,  405984  union all
  select '2004-11-17', 0603, 031, NULL,  408565  union all
  select '2004-12-15', 0385, 025, NULL,  415282  union all
  select '2004-12-31', 0516, 037, NULL,  418471  union all
  select '2005-01-15', 0975, 034, NULL,  422479  union all
  select '2005-02-15', 0941, 076, NULL,  431048  union all
  select '2005-03-16', 0702, 088, NULL,  439956  union all
  select '2005-04-09', 0359, 022, NULL,  446224  union all
  select '2005-04-23', 0304, 019, NULL,  450653  union all
  select '2005-05-05', 0526, 051, NULL,  454408  union all
  select '2005-05-27', 0383, 069, NULL,  461272  union all
  select '2005-06-08', 0919, 044, NULL,  464548  union all
  select '2005-07-12', 0462, 029, NULL,  474189  union all
  select '2005-07-21', 0152, 027, NULL,  476741  union all
  select '2005-07-27', 0275, 021, NULL,  478760  union all
  select '2005-08-04', 0293, 028, NULL,  480707  union all
  select '2005-08-10', 0288, 035, NULL,  482550  union all
  select '2005-08-17', 0249, 044, NULL,  484367  union all
  select '2005-08-24', 0194, 004, NULL,  486258  union all
  select '2005-09-06', 0141, 048, NULL,  489623  union all
  select '2005-09-08', 0294, 056, NULL,  490121  union all
  select '2005-09-13', 0427, 048, NULL,  491469  union all
  select '2005-09-22', 0270, 023, NULL,  494181  union all
  select '2005-09-29', 0478, 034, NULL,  496105  union all
  select '2005-10-09', 0248, 028, NULL,  498505  union all
  select '2005-10-12', 0560, 029, NULL,  499711  union all
  select '2005-10-30', 0122, 031, NULL,  504009  union all
  select '2005-11-01', 0096, 011, NULL,  504562  union all
  select '2005-11-02', 0310, 022, NULL,  504834  union all
  select '2005-11-08', 0205, 032, NULL,  506825  union all
  select '2005-11-16', 0646, 106, NULL,  509130  union all
  select '2005-12-06', 0358, 052, NULL,  514544  union all
  select '2005-12-14', 0291, 024, NULL,  516577  union all
  select '2005-12-20', 0263, 031, NULL,  518012  union all
  select '2005-12-26', 0293, 031, NULL,  519073
)
, split as (
     select
	     posted
       ,
	     cast(to_char(posted, 'YY')  as integer) + 
         cast(
            case
		    when to_char(posted, 'MM') = '12' and to_char(posted, 'DD') = '31'
            then  1
	        else  0
            end 
         as integer) as Y
      , 
         case
		 when to_char(posted, 'MM') = '12' AND TO_CHAR(posted, 'DD') = '31'
         then    '01'
		 else to_char(posted, 'MM')
		 end 
                             as M
      ,  votes
      ,  replies
      , rep
      , id
    from
		poll
)

${mainsql[1]} /*
select
	Y,
	M,
	round(avg(votes))     as votes,
	round(avg(replies))   as replies
from split
group by rollup(Y, M)
order by Y, M
${mainsql[1]} */

${mainsql[2]} /*
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 as say   -- split All Years
where say.m = split.M
group by say.M
order by say.M

${mainsql[2]} */

${mainsql[3]} /*

select
    Y,
    M,
    count(*) total
from split
group by rollup(Y, M)
order by Y, M

${mainsql[3]} */
;

" 
mainsql[${num}]=''

done | psql