title: Aggregating the most recent joined records per week
body: <p>I have an <code>updates</code> table in Postgres is 9.4.5 like this:</p>
  <pre><code>goal_id    | created_at | status
  1          | 2016-01-01 | green
  1          | 2016-01-02 | red
  2          | 2016-01-02 | amber
  </code></pre>
  <p>And a <code>goals</code> table like this:</p>
  <pre><code>id | company_id
  1  | 1
  2  | 2
  </code></pre>
  <p>I want to create a chart for each company that shows the state of all of their goals, per week.</p>
  <p><a href="https://i.stack.imgur.com/3pDez.png" rel="noreferrer"><img src="https://i.stack.imgur.com/3pDez.png" alt="example chart" /></a></p>
  <p>I image this would require to generate a series of the past 8 weeks, finding the most recent update for each goal that came before that week, then counting the different statuses of the found updates.</p>
  <p>What I have so far:</p>
  <pre><code>SELECT EXTRACT(year from generate_series) AS year, 
         EXTRACT(week from generate_series) AS week,
         u.company_id,
         COUNT(*) FILTER (WHERE u.status = 'green') AS green_count,
         COUNT(*) FILTER (WHERE u.status = 'amber') AS amber_count,
         COUNT(*) FILTER (WHERE u.status = 'red') AS red_count
  FROM generate_series(NOW() - INTERVAL '2 MONTHS', NOW(), '1 week')
  LEFT OUTER JOIN (
    SELECT DISTINCT ON(year, week)
           goals.company_id,
           updates.status, 
           EXTRACT(week from updates.created_at) week,
           EXTRACT(year from updates.created_at) AS year,
           updates.created_at 
    FROM updates
    JOIN goals ON goals.id = updates.goal_id
    ORDER BY year, week, updates.created_at DESC
  ) u ON u.week = week AND u.year = year
  GROUP BY 1,2,3
  </code></pre>
  <p>But this has two problems. It seems that the join on <code>u</code> isn't working as I thought it would. It seems to be joining on every row (?) returned from the inner query as well as this only selects the most recent update that happened from that week. It <em>should</em> grab the most recent update from before that week if it needs to.</p>
  <p>This is some pretty complicated SQL and I love some input on how to pull it off.</p>
  <h3>Table structures and info</h3>
  <p>The goals table has around ~1000 goals ATM and is growing about ~100 a week:</p>
  <pre><code>                                           Table &quot;goals&quot;
       Column      |            Type             |                         Modifiers
  -----------------+-----------------------------+-----------------------------------------------------------
   id              | integer                     | not null default nextval('goals_id_seq'::regclass)
   company_id      | integer                     | not null
   name            | text                        | not null
   created_at      | timestamp without time zone | not null default timezone('utc'::text, now())
   updated_at      | timestamp without time zone | not null default timezone('utc'::text, now())
  Indexes:
      &quot;goals_pkey&quot; PRIMARY KEY, btree (id)
      &quot;entity_goals_company_id_fkey&quot; btree (company_id)
  Foreign-key constraints:
      &quot;goals_company_id_fkey&quot; FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT
  </code></pre>
  <p>The <code>updates</code> table has around ~1000 and is growing around ~100 a week:</p>
  <pre><code>                                         Table &quot;updates&quot;
     Column   |            Type             |                            Modifiers
  ------------+-----------------------------+------------------------------------------------------------------
   id         | integer                     | not null default nextval('updates_id_seq'::regclass)
   status     | entity.goalstatus           | not null
   goal_id    | integer                     | not null
   created_at | timestamp without time zone | not null default timezone('utc'::text, now())
   updated_at | timestamp without time zone | not null default timezone('utc'::text, now())
  Indexes:
      &quot;goal_updates_pkey&quot; PRIMARY KEY, btree (id)
      &quot;entity_goal_updates_goal_id_fkey&quot; btree (goal_id)
  Foreign-key constraints:
      &quot;updates_goal_id_fkey&quot; FOREIGN KEY (goal_id) REFERENCES goals(id) ON DELETE CASCADE
  
   Schema |       Name        | Internal name | Size | Elements | Access privileges | Description
  --------+-------------------+---------------+------+----------+-------------------+-------------
   entity | entity.goalstatus | goalstatus    | 4    | green   +|                   |
          |                   |               |      | amber   +|                   |
          |                   |               |      | red      |                   |
  </code></pre>