Aggregating the most recent joined records per week

Viewed 675

I have an updates table in Postgres is 9.4.5 like this:

goal_id    | created_at | status
1          | 2016-01-01 | green
1          | 2016-01-02 | red
2          | 2016-01-02 | amber

And a goals table like this:

id | company_id
1  | 1
2  | 2

I want to create a chart for each company that shows the state of all of their goals, per week.

example chart

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.

What I have so far:

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

But this has two problems. It seems that the join on u 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 should grab the most recent update from before that week if it needs to.

This is some pretty complicated SQL and I love some input on how to pull it off.

Table structures and info

The goals table has around ~1000 goals ATM and is growing about ~100 a week:

                                           Table "goals"
     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:
    "goals_pkey" PRIMARY KEY, btree (id)
    "entity_goals_company_id_fkey" btree (company_id)
Foreign-key constraints:
    "goals_company_id_fkey" FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT

The updates table has around ~1000 and is growing around ~100 a week:

                                         Table "updates"
   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:
    "goal_updates_pkey" PRIMARY KEY, btree (id)
    "entity_goal_updates_goal_id_fkey" btree (goal_id)
Foreign-key constraints:
    "updates_goal_id_fkey" 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      |                   |
3 Answers
Related