Crosstab Views

Also known as pivot tables, distributing categories across colums is a very intuative way to display query results. Some built-in methods exist in PostgreSQL

• The psql \crosstab command provides a quick way to reformat the output of an interactive query, but is of no use for applications.
• The crosstab functions from the contrib module are awkward to use since they take a query as input, and has limited means of customization.

But we can assemble these using aggregate functions or CTEs.

JSON Aggregates

Returning a JSON object pushes the difficulty of presentation on the client, and is able to handle any number of keys/column names

WITH range AS (
    SELECT generate_series(now() - interval '10 days', now(), '1 day'::interval)::date AS day
),
bins AS (
  SELECT sent::date, subject, count(*)
  FROM notify_log
  GROUP BY sent::date, subject
)
SELECT
  range.day,
  CASE WHEN count(bins.subject) > 0 THEN
    jsonb_object_agg(COALESCE(bins.subject, ''), bins.count)
  END
FROM range
LEFT JOIN bins ON (bins.sent = range.day)
GROUP BY day
ORDER BY day

Example results:

    day     |                     case
------------+-----------------------------------------------
 2025-01-03 | {"Account Link": 1, "Usercode Activation": 1}
 2025-01-04 | {"Account Link": 1}
 2025-01-05 | {"Sitecode Activation": 1}
 2025-01-06 | {"Account Link": 5}
 2025-01-07 | {"Account Link": 1, "Usercode Activation": 1}
 2025-01-08 | {"Account Link": 3, "Usercode Activation": 1}
 2025-01-09 | {"Account Link": 1}
 2025-01-10 | {"Account Link": 3}
 2025-01-11 | {"Account Link": 3}
 2025-01-12 | {"Account Link": 3}
(10 rows)

Joining Multiple Queries

If the number of columns can be fixed, one query can be run to collect data for each column. This method is repedative, but provides the ability to adjust the results in any way imaginable

WITH range AS (
  SELECT generate_series(now() - interval '10 days', now(), '1 day'::interval)::date AS day
),
bin1 AS (
  SELECT sent::date, count(*)
  FROM notify_log
  WHERE subject = 'Account Link'
  GROUP BY sent::date
),
bin2 AS (
  SELECT sent::date, count(*)
  FROM notify_log
  WHERE subject = 'Usercode Activation'
  GROUP BY sent::date
),
bin3 AS (
  SELECT sent::date, count(*)
  FROM notify_log
  WHERE subject = 'Sitecode Activation'
  GROUP BY sent::date
)
SELECT
  range.day,
  bin1.count AS account_link,
  bin2.count AS usercode_activation,
  bin3.count AS sitecode_activation
FROM range
LEFT JOIN bin1 ON (bin1.sent = day)
LEFT JOIN bin2 ON (bin2.sent = day)
LEFT JOIN bin3 ON (bin3.sent = day)
ORDER BY day

Example results:

    day     | account_link | usercode_activation | sitecode_activation
------------+--------------+---------------------+---------------------
 2025-01-03 |            1 |                   1 |
 2025-01-04 |            1 |                     |
 2025-01-05 |              |                     |                   1
 2025-01-06 |            5 |                     |
 2025-01-07 |            1 |                   1 |
 2025-01-08 |            3 |                   1 |
 2025-01-09 |            1 |                     |
 2025-01-10 |            3 |                     |
 2025-01-11 |            3 |                     |
 2025-01-12 |            3 |                     |
(10 rows)