title: MySQL query to aggregate the number of versions created over previous 7 days
body: <p>I have a table which stores versions of Products (<code>product_versions</code>). These product versions
  can have a set of statuses (<code>active</code>, <code>draft</code>, <code>archived</code>) and I need get a result set of the  aggregate of these statuses for each day (historically) so that a graph can be generated.</p>
  
  <p>While I know that I can use application code (PHP) or database triggers to build and maintain an aggregate table, I am hoping to solve this in a query (partly because I want it to be elegant, and partly because I'm curious about how to do it). If I can achieve this, I can then cache the result or similar.</p>
  
  <p>So far I am able to get the result for a single status like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
      pv.created_at_date,
      (
          SELECT CONCATcount(*)
          FROM `product_versions` p
          JOIN (
              SELECT product_id, MAX(id) AS latest_version
              FROM product_versions
              GROUP BY product_id
          ) grouped_versions ON p.product_id = grouped_versions.product_id AND p.id = grouped_versions.latest_version
          WHERE created_at_date = pv.created_at_date
          AND status = 'draft'
          ORDER BY id ASC
      ) as draft_status_count_subquery,
  FROM product_versions pv
  WHERE created_at_date &gt;= date_sub(now(), interval 7 day)
  GROUP BY pv.created_at_date
  ORDER BY pv.created_at_date desc
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+-----------------+-----------------------------+
  | created_at_date | draft_status_count_subquery |
  +-----------------+-----------------------------+
  | 2013-09-09      |                           0 |
  | 2013-09-06      |                          26 |
  | 2013-09-05      |                          40 |
  | 2013-09-04      |                          46 |
  +-----------------+-----------------------------+
  </code></pre>
  
  <p>It's important to note that I need to be able to determine the status of the latest version for each day (using the grouped_versions sub-query) in order to return correct aggregates.</p>
  
  <p>All my other attempts to join in additional statuses have not worked (besides the obvious which is to add additional sub-queries (status_count_subquery 1 .. n) for each status which I'm reluctant to do because the performance is already quite slow with only 1 sub-query).</p>
  
  <p>Thus, I ask the world out there, is there a better way to achieve this result (or similar)?</p>
  
  <p><strong>Test data</strong></p>
  
  <p>Here is some sample data to re-create the scenario:</p>
  
  <pre><code>CREATE TABLE `product_versions` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `product_id` int(10) unsigned DEFAULT NULL,
    `created_at_date` date DEFAULT NULL,
    `title` varchar(100) DEFAULT NULL,
    `status` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  INSERT INTO `product_versions` (`id`, `product_id`, `created_at_date`, `title`, `status`)
  VALUES
      (1, 1, '2013-09-06', 'Foo Product', 'draft'),
      (2, 1, '2013-09-06', 'Foo Product', 'active'),
      (3, 2, '2013-09-06', 'Bar Product', 'draft'),
      (4, 1, '2013-09-07', 'Foo Product', 'archived'),
      (5, 2, '2013-09-07', 'Bar Product', 'active'),
      (6, 3, '2013-09-07', 'Baz Product', 'draft'),
      (7, 4, '2013-09-07', 'Fiz Product', 'draft');
  </code></pre>
  
  <p><strong>Hoping for an output like this</strong></p>
  
  <pre><code>+-----------------+-------+--------+----------+
  | created_at_date | draft | active | archived |
  +-----------------+-------+--------+----------+
  | 2013-09-07      |     2 |      1 |        1 |
  | 2013-09-06      |     1 |      1 |        0 |
  +-----------------+-------+--------+----------+
  </code></pre>