-- Make sure we don't pop up a pagre for the crossjoins_column query
\pset pager off

CREATE TEMPORARY TABLE logit_top_bangers (
  who_nick varchar(32),
  bang_count int
);

INSERT INTO logit_top_bangers (
  SELECT
    who_nick,
    sum(length(regexp_replace(data, '[^!]', '', 'g'))) AS num_bangs
  FROM logit
  GROUP BY who_nick
  ORDER BY num_bangs DESC
  LIMIT 20
);

SELECT string_agg(col, ',') AS crossjoin_columns FROM (
  SELECT quote_ident(who_nick) || ' float' AS col
  FROM logit_top_bangers
  ORDER BY bang_count DESC
) AS cols;
\gset

-- CSV
\pset format unaligned
\pset footer off
\pset fieldsep ,
\o bangs-by-bangline.csv
SELECT *
FROM
  crosstab($$
    SELECT
      date_trunc('month', ts) AS time,
      who_nick,
      sum(length(regexp_replace(data, '[^\!]', '', 'g'))) / COUNT(*)::float AS num_bangs
    FROM logit
    WHERE
      type='PUB' AND
      who_nick IN (SELECT who_nick FROM logit_top_bangers) AND
      data LIKE '%!%'
    GROUP BY time,who_nick
    ORDER BY time
  $$, 'SELECT who_nick FROM logit_top_bangers ORDER BY bang_count DESC'
) AS (
  ts timestamp without time zone,
  :crossjoin_columns
);
\o