title: Generate date-histogram over table
body: <p>I wrote a query that returns a bar graph in the terminal using Postgres CLI. The query is slow and inefficient. I would like to change that.</p>
  
  <p>At the base, we have a pretty simple query. We want each row to be a division of the total number of rows in our table. Let's say that our hardcoded number of rows is <code>N_ROWS</code>, and our table is <code>my_table</code>.</p>
  
  <p>Also, let's say <code>N_ROWS</code> equals 8.</p>
  
  <pre><code>select
      (select count(id) from my_table) / N_ROWS * (N_ROWS - num) as level 
  from (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)) as t (num)
  </code></pre>
  
  <p>In my case, this returns my Y-Axis of the chart as:</p>
  
  <pre><code> level
  -------
   71760
   62790
   53820
   44850
   35880
   26910
   17940
    8970
       0
  </code></pre>
  
  <p>You can see the issues with that query already.</p>
  
  <p>Can I programmatically generate a number of rows using <code>N_ROWS</code> and not hardcode each row value in <code>VALUES</code>? I also don't like how I perform a new count over my whole table for each row, obviously.</p>
  
  <p>We now need our X-Axis, and this is what I came up with:</p>
  
  <pre><code>select
      r.level,
      case
          when (
              select count(id) from my_table where created_at_utc&lt;= '2019-01-01 00:00:00'::timestamp without time zone
          ) &gt;= r.level then true
      end as "2019-01-01"
  from (
      select (select count(id) from my_table) / N_ROWS * (N_ROWS - num) as level from (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)) as t (num)
  ) as r;
  </code></pre>
  
  <p>Which returns our first bucket:</p>
  
  <pre><code> level | 2019-01-01
  -------+------------
   71760 |
   62790 |
   53820 |
   44850 |
   35880 |
   26910 | t
   17940 | t
    8970 | t
       0 | t
  </code></pre>
  
  <p>I'd rather not hardcode a case statement for each bucket, but, of course, that's what I did. The results are what I was looking for.</p>
  
  <pre><code> level | 2019-01-01 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 2019-12-01
  -------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
   71760 |            |            |            |            |            |            |            |            |            |            |            | t
   62790 |            |            |            |            | t          | t          | t          | t          | t          | t          | t          | t
   53820 |            |            |            | t          | t          | t          | t          | t          | t          | t          | t          | t
   44850 |            |            | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
   35880 |            | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
   26910 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
   17940 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
    8970 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
       0 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
  </code></pre>