The problem

I was faced with implementing a dashboard on a recent project. The client asked to see their data sliced up by category along with running monthly and yearly breakdowns.

The data to an approximation looked like this: There are Posts and Rejection. A Rejection references a Post and gives a reason for rejection. Something like:

posts
  id
  body

rejections
  id
  reason
  post_id

Posts are written and moderators can reject them, giving a reason ("spam", "boring", "mansplaining"). We want to see how many posts are rejected monthly and yearly in the current year first overall and then grouped by reason.

How are we going to do this? We could make a big table joining a series of subqueries, and then have Rails read the table columns first, pivoting the output for us. That give me the no-feeling pretty quick as I hope it does for you also.

Reaching out

I sat on this idea for a while, and then I did what I always do when I have a Postgres question: ask Shey. Shey had a one word answer for me: crosstabs.

Making it happen

We’ll start with the overall rejections for this year, broken down by month.

Enabling tablefunc

First we need to enable the postgresql extension for crosstabs: tablefunc

# enable tablefunc extension for crosstab support
$ create extension if not exists tablefunc;

Here is my initial table:

create view overall as
  select      count(rejections.id),
              date_part('month', rejections.created_at) as month
  from        rejections
  where       date_part('year', rejections.created_at) = date_part('year', current_date)
  group by    date_part('month', rejections.created_at);

create view report_overall as
  select * from crosstab(
    'select ''overall'', month, count from overall',
    'select m from generate_series(1,12) m'
  ) as (
    "overall" text,
    "jan" int,
    "feb" int,
    "mar" int,
    "apr" int,
    "may" int,
    "jun" int,
    "jul" int,
    "aug" int,
    "sep" int,
    "oct" int,
    "nov" int,
    "dec" int
  );

select * from report_overall;
 overall | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 overall |   2 |   1 |   2 |     |   1 |   2 |   1 |   4 |     |   4 |   2 |   1

That's pretty close to what I need, thank you Shey.

Need more

That’s great, but we need a way to add the YTD info. Since it will be the same columns, we can combine it with the monthly data using a union. We just need to add a column at the end of our crosstab, and add a hacked-on 13th month.

create view overall as
  select      count(rejections.id),
              date_part('month', rejections.created_at) as month
  from        rejections
  where       date_part('year', rejections.created_at) = date_part('year', current_date)
  group by    date_part('month', rejections.created_at)

  union

  select      count(rejections.id),
              13 as month
  from        rejections
  where       date_part('year', rejections.created_at) = date_part('year', current_date);

create view report_overall as
  select * from crosstab(
    'select ''overall'', month, count from overall',
    'select m from generate_series(1,13) m'
  ) as (
    "overall" text,
    "jan" int,
    "feb" int,
    "mar" int,
    "apr" int,
    "may" int,
    "jun" int,
    "jul" int,
    "aug" int,
    "sep" int,
    "oct" int,
    "nov" int,
    "dec" int,
    "ytd" int
  );
select * from report_overall;
 overall | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec | ytd
---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 overall |   2 |   1 |   2 |     |   1 |   2 |   1 |   4 |     |   4 |   2 |   1 |  20

Thoroughly hacked.

Keep it coming

That'll do for the overall data, but we'd like to see the same info broken out by rejection reason.

create view rejection_reasons as
  select      count(rejections.id),
              reason_id,
              date_part('month', rejections.created_at) as month
  from        rejections
  where       date_part('year', rejections.created_at) = date_part('year', current_date)
  group by    reason_id,
              date_part('month', rejections.created_at)

  union

  select      count(rejections.id),
              reason_id,
              13 as month
  from        rejections
  where       date_part('year', rejections.created_at) = date_part('year', current_date)
  group by    reason_id;

create view report_rejection_reasons as
  select * from crosstab(
    'select reason_id, month, count from rejection_reasons order by reason_id',
    'select m from generate_series(1,13) m'
  ) as (
    "reason" text,
    "jan" int,
    "feb" int,
    "mar" int,
    "apr" int,
    "may" int,
    "jun" int,
    "jul" int,
    "aug" int,
    "sep" int,
    "oct" int,
    "nov" int,
    "dec" int,
    "ytd" int
  );
select * from report_rejection_reasons;
 reason | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec | ytd
--------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 0      |   1 |     |   2 |     |     |     |     |   1 |     |   2 |   2 |     |   8
 1      |   1 |     |     |     |   1 |   1 |     |   1 |     |   1 |     |     |   5
 2      |     |   1 |     |     |     |   1 |   1 |   2 |     |   1 |     |   1 |   7

It works... but it's ugly

Okay that’s pretty good but that is some ugly SQL. Lets clean up with some Common Table Expressions:

create view rejection_reasons as
  with

  monthly as (
    select      count(rejections.id),
                reason,
                date_part('month', rejections.created_at) as month
    from        rejections
    where       date_part('year', rejections.created_at) = date_part('year', current_date)
    group by    reason,
                date_part('month', rejections.created_at)
  ),

  yearly as (
    select      count(rejections.id),
                reason,
                13 as month
    from        rejections
    where       date_part('year', rejections.created_at) = date_part('year', current_date)
    group by    reason;
  )

  select * from monthly
  union
  select * from yearly;

It's not a big change, but it helps to give meaning and structure to the statement.

Lets use it.

Now that we've got our report, lets see how we can use it nicely in rails.

Create a Report model that is backed by a view:

class RejectionReasonReport < ActiveRecord::Base
  self.table_name = 'report_rejection_reasons'

  def each_column
    %i(jan feb mar apr may jun jul aug sep oct nov dec ytd).map do |col|
      yield public_send(col)
    end
  end

  def title
    %w(Spam Boring Mansplaining)[reason_id.to_i]
  end
end

Create a controller that loads the data

class ReportsController < ApplicationController

  def rejection_reasons
    @report = RejectionReasonReport.all
  end

end

Create a view that displays it for us:

<table>
  <thead>
    <th></th>
    <th>Jan</th>
    <th>Feb</th>
    <th>Mar</th>
    <th>Apr</th>
    <th>May</th>
    <th>Jun</th>
    <th>Jul</th>
    <th>Aug</th>
    <th>Sep</th>
    <th>Oct</th>
    <th>Nov</th>
    <th>Dec</th>
    <th>YTD</th>
  </thead>
  <tbody>
    <% @report.each do |row| %>
      <tr>
        <td><%= row.title %></td>
        <% row.each_column do |count| %>
          <td><%= count %></td>
        <% end %>
      </tr>
    <% end %>
  </tbody>
</table>

rejections by reason